Select all columns but also by distinct column and oldest date

user_19815685 0
Viewed 55

I need to select rows that are distinct by number and are the oldest date.

I saw this post: SQL Select Distinct column and latest date

But it doesn't work in my case, because I need to select all the columns, not just the 2 that are used in the clause.

So I get this error:

it is not contained in either an aggregate function or the GROUP BY clause` error.

Is there a way to choose all the columns, and in case there are rows with same date, consider the older one as the one with the lower id?

sample data:

id      name       number         date
1       foo         1111       06-11-2022
2       bar         2222       01-12-2022
3       baz         3333       12-30-2022
4      foobar       1111       02-01-2022

this is the query I tried:

SELECT id, name, number, MIN(date) as date
FROM my_table
GROUP BY number

using Microsoft SQL

tsql sql-server sql
1 Answers

When you are using GROUP BY aggregate function, you can only use the aggregated columns in your SELECT list. You can achieve your expected output in several ways, Here is one using windowed aggregate function :

select T.*
from (select *,
             row_number() over (partition by number order by [date], id) as sn
      from my_table
     ) T
where sn = 1;

SQL here

user_56959530
Related