Sql Select First Column And For Each Row Select Unique Id And The Last Date

May 19, 2024 Post a Comment

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

Solution 1:

declare@ttable (ID INT,Col2 INT,DateINT)
insertinto@t(ID,Col2,Date)values (1,1,2001)
insertinto@t(ID,Col2,Date)values (1,2,2001)
insertinto@t(ID,Col2,Date)values (1,3,2001)
insertinto@t(ID,Col2,Date)values (1,4,2001)
insertinto@t(ID,Col2,Date)values (2,1,2002)
insertinto@t(ID,Col2,Date)values (2,2,2002)
insertinto@t(ID,Col2,Date)values (2,3,2002)
insertinto@t(ID,Col2,Date)values (2,4,2002)

;with cte as(
    select*,
        rn =row_number() over(partitionby ID orderby Col2 desc)
    from@t

)
select
    ID,
    Col2,
    Datefrom cte
where
    rn =1

Solution 2:

SELECT ID,MAX(Col2),MAX(Date) FROM tableName GROUPBY ID

Solution 3:

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

SELECT ID, MAX(COL2), MAX(DATE)
FROM Table1
GROUPBY ID

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

SELECT yourtable.ID, sub1.COL2, sub1.DATEFROM yourtable 
INNER JOIN -- trywith CROSS APPLY for performance AND without ON1=1
(SELECT TOP 1 COL2, DATEFROM yourtable sub2
 WHERE sub2.ID = topquery.ID
 ORDERBY COL2, DATE) sub1 ON1=1

Solution 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.DATEFROM tbl m
  LEFT JOIN tbl o ON m.ID = o.ID AND m.DATE < o.DATEWHERE o.DATEis NULL
ORDERBY 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.

Solution 5:

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

SELECT ID, MAX(DATE) 
FROM table_name 
GROUPBY 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.

Lab Sql