RoLYroLLs RoLYroLLs - 3 years ago 69
SQL Question

UNION CTE with ORDER BY on 3 Tables

This is not easy to explain, however, I do believe there is a much nicer way to do what I managed to do and hope to get some help.

I have 3 tables (

T1
,
T2
, and
T3
). I need to get the latest from
T1
and
T2
, then with those results return the results from
t3
or from the previous result if
t3
is empty. So the
LatestDate
doesn't really matter if there is a record in
t3
. Also, if there is no data in
t3
and the
LatestDate
is the same on
t1
and
t2
(rarely will happen, but want to plan accordingly), I want results from
t1
.

Here's a sample of what I got, mind you the actual query is has many more fields, but the concept is the same.

CREATE TABLE [dbo].[t1](
[Id] [INT] NOT NULL,
[LatestDate] [DATETIME] NOT NULL
);
CREATE TABLE [dbo].[t2](
[Id] [INT] NOT NULL,
[LatestDate] [DATETIME] NOT NULL
);
CREATE TABLE [dbo].[t3](
[Id] [INT] NOT NULL,
[LatestDate] [DATETIME] NOT NULL
);
INSERT t1 (Id, LatestDate) VALUES (1, CAST(N'2000-01-01T00:00:00.000' AS DateTime));
INSERT t1 (Id, LatestDate) VALUES (2, CAST(N'2001-01-01T00:00:00.000' AS DateTime));
INSERT t1 (Id, LatestDate) VALUES (3, CAST(N'2002-01-01T00:00:00.000' AS DateTime));
INSERT t2 (Id, LatestDate) VALUES (1, CAST(N'2001-01-01T00:00:00.000' AS DateTime));
INSERT t2 (Id, LatestDate) VALUES (2, CAST(N'2002-01-01T00:00:00.000' AS DateTime));
INSERT t2 (Id, LatestDate) VALUES (4, CAST(N'2003-01-01T00:00:00.000' AS DateTime));
INSERT t3 (Id, LatestDate) VALUES (1, CAST(N'2001-01-01T00:00:00.000' AS DateTime));
INSERT t3 (Id, LatestDate) VALUES (2, CAST(N'2000-01-01T00:00:00.000' AS DateTime));
INSERT t3 (Id, LatestDate) VALUES (5, CAST(N'2004-01-01T00:00:00.000' AS DateTime));
GO;

WITH CTE AS (
SELECT TOP 1 * FROM (
SELECT 2 AS Sort, * FROM t1 WHERE t1.id = @UserId
UNION
SELECT 3 AS Sort, * FROM t2 WHERE t2.id = @UserId
) AS t
ORDER BY
t.LatestDate DESC
)
SELECT TOP 1 * FROM (
SELECT TOP 1 * FROM CTE
UNION
SELECT 1 AS Sort, * FROM t3 WHERE t3.id = @UserId
) AS t
ORDER BY
t.Sort;


Expected results:

When @UserID = 1:

Sort Source Id LatestDate
1 t3 1 1/1/2001

When @UserID = 2:

Sort Source Id LatestDate
1 t3 2 1/1/2000

When @UserID = 3:

Sort Source Id LatestDate
2 t1 3 1/1/2002

When @UserID = 4:

Sort Source Id LatestDate
3 t2 4 1/1/2003

When @UserID = 5:

Sort Source Id LatestDate
1 t3 5 1/1/2004


Thanks!

Answer Source

If I understand you correctly you want something like:

DECLARE @UserID INT  = 5;

WITH cte AS (
    SELECT 1 AS src, * FROM  t1 WHERE id = @UserId
    UNION ALL SELECT 1, * FROM t2 WHERE id = @UserId
    UNION ALL SELECT 3, * FROM t3 WHERE id = @UserId
), cte2 AS (
    SELECT * , ROW_NUMBER() OVER(ORDER BY src DESC, LatestDate DESC) AS rn
    FROM cte
)
SELECT Id, LatestDate
FROM cte2
WHERE rn = 1;

RextesterDemo


Using PARTITION BY you could move filtering @userId to final part:

DECLARE @UserID INT  = 5;

WITH cte AS (
    SELECT 1 AS src, * FROM  t1 
    UNION ALL SELECT 1, * FROM t2 
    UNION ALL SELECT 3, * FROM t3 
), cte2 AS (
    SELECT *
     ,ROW_NUMBER() OVER(PARTITION BY Id ORDER BY src DESC, LatestDate DESC) AS rn
    FROM cte
)
SELECT Id, LatestDate
FROM cte2
WHERE rn = 1
  AND id = @UserID

Rextester Demo2

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download