SQL Select First column and for each row select unique ID and the last date

Question

I have a problems this mornig , I have tried many solutions and nothing gave me the expected result.

I have a table that looks like this :

+----+----------+-------+
| ID | COL2     | DATE  | 
+----+----------+-------+
|  1 | 1        |  2001 | 
|  1 | 2        |  2002 | 
|  1 | 3        |  2003 | 
|  1 | 4        |  2004 | 
|  2 | 1        |  2001 | 
|  2 | 2        |  2002 | 
|  2 | 3        |  2003 | 
|  2 | 4        |  2004 | 
+----+----------+-------+

And I have a query that returns a result like this : I have the unique ID and for this ID I want to take the last date of the ID

+----+----------+-------+
| ID | COL2     | DATE  | 
+----+----------+-------+
|  1 | 4        |  2004 | 
|  2 | 4        |  2004 | 
+----+----------+-------+

But I don't have any idea how I can do that. I tried Join , CROSS APPLY ..

If you have some idea ,

Thank you

Clement FAYARD

Answer 1

declare @t table (ID INT,Col2 INT,Date INT)
insert into @t(ID,Col2,Date)values (1,1,2001)
insert into @t(ID,Col2,Date)values (1,2,2001)
insert into @t(ID,Col2,Date)values (1,3,2001)
insert into @t(ID,Col2,Date)values (1,4,2001)
insert into @t(ID,Col2,Date)values (2,1,2002)
insert into @t(ID,Col2,Date)values (2,2,2002)
insert into @t(ID,Col2,Date)values (2,3,2002)
insert into @t(ID,Col2,Date)values (2,4,2002)

;with cte as(
    select
        *,
        rn = row_number() over(partition by ID order by Col2 desc)
    from @t

)
select
    ID,
    Col2,
    Date
from cte
where
    rn = 1

Answer 2

If col2 and date allways the highest value in combination than you can try

SELECT ID, MAX(COL2), MAX(DATE)
FROM Table1
GROUP BY ID

But it is not realy good. The alternative is a subquery with:

SELECT yourtable.ID, sub1.COL2, sub1.DATE
FROM yourtable 
INNER JOIN -- try with CROSS APPLY for performance AND without ON 1=1
(SELECT TOP 1 COL2, DATE
 FROM yourtable sub2
 WHERE sub2.ID = topquery.ID
 ORDER BY COL2, DATE) sub1 ON 1=1

Answer 3

In order to do this you MUST exclude COL2 Your query should look like this

SELECT ID, MAX(DATE) 
FROM table_name 
GROUP BY ID 

The above query produces the Maximum Date for each ID. Having COL2 with that query does not makes sense, unless you want the maximum date for each ID and COL2 In that case you can run:

SELECT ID, COL2, MAX(DATE) GROUP BY ID, COL2;

When you use aggregation functions(like max()), you must always group by all the other columns you have in the select statement.

I think you are facing this problem because you have some fundemental flaws with the design of the table. Usually ID should be a Primary Key (Which is Unique). In this table you have repeated IDs. I do not understand the business logic behind the table but it seems to have some flaws to me.

Answer 4

You didn't tell what's the name of your table so I'll assume below it is tbl:

SELECT m.ID, m.COL2, m.DATE
FROM tbl m
  LEFT JOIN tbl o ON m.ID = o.ID AND m.DATE < o.DATE
WHERE o.DATE is NULL
ORDER BY m.ID ASC

Explanation: The query left joins the table tbl aliased as m (for "max") against itself (alias o, for "others") using the column ID; the condition m.DATE < o.DATE will combine all the rows from m with rows from o having a greater value in DATE. The row having the maximum value of DATE for a given value of ID from m has no pair in o (there is no value greater than the maximum value). Because of the LEFT JOIN this row will be combined with a row of NULLs. The WHERE clause selects only these rows that have NULL for o.DATE (i.e. they have the maximum value of m.DATE).

Check the SQL Antipatterns: Avoiding the Pitfalls of Database Programming book for other SQL tips.

Answer 5

SELECT ID,MAX(Col2),MAX(Date) FROM tableName GROUP BY ID