How to sort rows after union

join;union

I have two tables that I want to combine.

I need to be able to show all DelDates that had changes from the original (record 1). Record 1 as the first Record and Record 0 (table 2) always being the last record.

Result should be:

I am new to SQL and with my research, it seems I need to use UNION to join both tables and to join the new table (from Union) by itself. When I tried doing that, it created duplicate rows and columns.

Best Answer

You are correct - if you have two datasets with identical columns then UNION is the correct way to append the second dataset onto the first. Moreover I would suggest you use UNION ALL. UNION by itself guarantees unique values in the output. Usually this requires a sort and further processing to detect and eliminate duplicates. In your case I do not think you have duplicates so UNION ALL would be appropriate.

The real problem seems to be the sort order. You want the combined dataset to be sorted by Record but with 0 as the last item. The rows exist in two blocks: the zero rows and the other rows. If we assign a "block number" to each they can be sorted accordingly. Clearly this cannot be achieved by changing Record itself so another column must be introduced. This new artificial column need not be returned form the query.

You don't mention which DBMS you use. I have SQL Server to hand; I'll use it. For future questions please do not include screen shots of the data. I makes it much too hard to reproduce your situation. Instead include in the quesiton the CREATEs and INSERTs required to reproduce the test cases and also what queries you've tried and why the don't work. See mcve. I've written the minimum to demonstrate the proposed solution.

drop table if exists Table1;
drop table if exists Table2;
go

create table Table1(Name varchar(9), Record int); -- other columns omitted for simplicity
create table Table2(Name varchar(9), Record int);
go

insert Table1 values ('Joy', 1), ('Joy', 2);
insert Table2 values ('Joy', 0);
go


-- I've written this as a Common Table Expression (CTE). A sub-query would work just as well
;with Data as
(
select
    Name, Record, BlockNumber = 2   -- BlockNumber for Table2 is greater than that for Table1
from Table2  -- the order of queries within the CTE does not affect the output

union all

select
    Name, Record, BlockNumber = 1
from Table1
)
select 
    Name, Record -- note that BlockNumber is not returned
from Data
order by
    Name,           -- the leading column of the sort so one person's rows all appear together
    BlockNumber,    -- will put Table2's row after Table1's.
    Record;         -- since all Table1's rows are in the same block they'll sort by Record

Related Solutions

Sql-server – How to make a union view execute more efficiently

The comments on the question show that the issue is that the test database the OP was using to develop the query had radically different data characteristics than the production database. It had much fewer rows and the field being used for filtering wasn't selective enough.

When the number of distinct values in a column is too small the index may not be sufficiently selective. In this case a sequential table scan is cheaper than an index seek/row lookup operation. Typically a table scan makes exensive use of sequential I/O, which is much faster than random access reads.

Often, if a query would return more than just a few percent of rows it will be cheaper just to do a table scan than an index seek/row lookup or similar operation that makes heavy use of random I/O.

MySQL - How to Select Max(Date) from Union Query

So it turns out that confluence uses a different structure to manage this data and now the discrepancies appear to be resolved.

Here is the query:

select
    User_Name,
    First_Name,
    Last_Name,
    max(Last_Login) as Last_Login
FROM
(
SELECT
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(a.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM jira.cwd_user u
  LEFT JOIN jira.cwd_user_attributes a ON u.id = a.user_id AND a.attribute_name = 'login.lastLoginMillis'
  JOIN jira.cwd_directory d ON u.directory_id = d.id
where u.active = 1
UNION
 SELECT 
    u.user_name, 
    u.first_name as First_Name, 
    u.last_name as Last_Name, 
    date_format(l.successdate, '%Y-%m-%d') as Last_Login 
  FROM confluence.logininfo l 
  JOIN confluence.user_mapping m ON m.user_key = l.username 
  JOIN confluence.cwd_user u ON m.username = u.user_name 
  JOIN confluence.cwd_directory d ON u.directory_id = d.id and u.active = 'T' 
UNION
select
    u.user_name as User_Name,
    u.first_name as First_Name,
    u.last_name Last_Name,
    ifnull(from_unixtime((cast(sa.attribute_value AS UNSIGNED)/1000), '%Y-%m-%d'), '1970-01-01') AS Last_Login
FROM stash.cwd_user u
LEFT JOIN stash.cwd_user_attribute sa ON u.id = sa.user_id AND sa.attribute_name = 'login.lastLoginMillis'
JOIN stash.cwd_directory d ON u.directory_id = d.id
where u.is_active = 'T'
) as z
group by User_Name, First_Name, Last_Name
having (max(Last_Login)) <= date_sub(now(), interval 90 day)
order by User_Name asc, Last_Login desc

Best Answer

Related Solutions

Sql-server – How to make a union view execute more efficiently

MySQL - How to Select Max(Date) from Union Query

Related Question