SELECT DISTINCT on multiple columns with new value

Question

I have these two tables.

Table #1:

+----+------+-----+
| ID | Y    | AGE | 
+----+------+-----+
|  1 | 2022  |  a | 
|  1 | 2022  |  b | 
|  3 | 2021  |  a | 
|  4 | 2021  |  a | 
|  4 | 2021  |  b | 
|  4 | 2021  |  c | 
|  7 | 2021  |  a |
|  7 | 2022  |  b |
+----+-------+----+

Table #2:

+----+------+-----------+
| ID | num  | something | 
+----+------+-----------+
|  1 | 10  |  a1221     | 
|  3 | 30  |  a4342     | 
|  4 | 40  |  bdssd     | 
|  7 | 70  |  asdsds    |
+----+-----+------------+

and I would like to merge them into this result set:

+----+-------+-----+
| ID | Y     | num | 
+----+-------+-----+
|  1 | 2022  |  10 |  
|  3 | 2021  |  30 | 
|  4 | 2021  |  40 | 
|  7 | 2021  |  70 |
|  7 | 2022  |  70 |
+----+-------+-----+

That means I would like take unique pairs of T1.ID and T1.Y ignoring column AGE and them INNER JOIN resulting table with T2 on T1.ID = T2.ID.

I know I can do this in steps as

WITH cte AS
(
    SELECT ID, Y 
    FROM T1
    GROUP BY ID, Y 
)
SELECT cte.ID, cte.Y, T2.num
FROM cte 
INNER JOIN T2 ON cte.ID = T2.ID

but is there any better way without creating a temporary table? Because simple

SELECT T1.ID, T1.Y, T2.num
FROM T1 
INNER JOIN T2 ON T1.ID = T2.ID

will result with duplicates that comes from T1.AGE even tho I'm not using it

Temporary table? Using a cte doesn't mean you have to get a temportaty table. the optimizer might decide something else. — jarlh, Sep 08 '22 at 08:29
Seems like a classic "`TOP 1` (or alternative rowset limiting operator) in each group" problem. — Thom A, Sep 08 '22 at 08:30
@jarlh maybe not "temporary table" but still it is like a "helper", that may be not efficient — Saguro, Sep 08 '22 at 08:31
@Larnu sorry, I did not know there are major differences between them - I'm using Microsoft SQL Server — Saguro, Sep 08 '22 at 08:51
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Thom A, Sep 08 '22 at 08:55

score 0 · Answer 1 · answered Sep 08 '22 at 10:14

0

I think it's better to use views for this:

CREATE VIEW dbo.view1
AS
SELECT
  ID
 ,Y
FROM T1
GROUP BY ID
        ,Y
GO

And call it wherever needed like tables:

SELECT v1.ID, v1.Y, T2.num
FROM view1 v1
INNER JOIN T2 ON v1.ID = T2.ID

answered Sep 08 '22 at 10:14

mohsen mashhadi

201
2
8

SELECT DISTINCT on multiple columns with new value

1 Answers1