2

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

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43
pileup
  • 1
  • 2
  • 18
  • 45
  • 4
    Would you mind showing us your code and sample data, so we can reproduce the isse? Also: which rdbms are you using? – Marco Aug 29 '22 at 15:59
  • Added information, I guess this error happens because `MIN` can have more than one result if the dates are equal? In my case, I should probably get the oldest by `id` as well – pileup Aug 29 '22 at 16:14
  • @Md.SumanKabir looks like it, didn't expect the query to be so complex for that haha. I wonder if there's a way to make it simpler like the query in the link in the post. But I guess not because of the aggregate problem. Because I simply need to get the latest of each number, sounds simple, but in SQL it's not :D – pileup Aug 29 '22 at 18:20

1 Answers1

4

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

Md. Suman Kabir
  • 5,243
  • 5
  • 25
  • 43