Get max date for each line in a table in SQL

Question

I edited my answer so the solution for my question is:

select  DISTINCT e.Cod_proiect
     , p.Descriere
     , p.Stare
     , p.ClientService
     , e.Data
  from ExpPoz as e 
  join Proiecte as p 
    on e.Cod_proiect=p.Cod_proiect 
             join ( 
                     select cod_proiect, max(data) maxDt 
                     from ExpPoz
                     group by cod_proiect 
                  ) latest on latest.cod_proiect = e.cod_proiect and latest.maxDt = e.Data and p.Stare='I'

Which gives the following error: Subquery returned more than 1 value I am trying to get every line with the maximum date.

I have the following table structure:

a    date1
a    date2
a    date3
b    date4
b    date5

The output should be, supposing that date3 and date5 are the oldest/biggest:

a   date3
b   date5

Thanks in advance.

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Sebastian Brosch, Feb 22 '18 at 15:19
my example is using 2 tables not just one..that's why returns the error : Subquery returned more than 1 value — user1147188, Feb 22 '18 at 15:20
You really should start using ANSI-92 style joins, they have been available for more than 25 years now. — Sean Lange, Feb 22 '18 at 15:21

Rigerta · Accepted Answer · 2018-02-22T15:38:14.350

1

This should do the trick:

select  e.Cod_proiect
      , p.Descriere
      , p.Stare
      , p.ClientService
from ExpPoz as e join Proiecte as p on e.Cod_proiect=p.Cod_proiect 
                 join ( 
                         select cod_proiect, max(data) maxDt 
                         from ExpPoz
                         group by cod_proiect 
                      ) latest on latest.cod_proiect = e.cod_proiect and latest.maxDt = e.Data
where p.Stare='I'

Please note that the way you have written the joins is very old and it is better and more clear if you use the modern join style. In this query I selected everything you need and joined once with the latest record per cod_proiect.

edited Feb 22 '18 at 15:38

answered Feb 22 '18 at 15:21

Rigerta

3,959
15
26

The multi-part identifier "p.Stare" could not be bound. – user1147188 Feb 22 '18 at 15:26
1

see my edited answer..just needed a distinct e.Cod_proiect – user1147188 Feb 22 '18 at 15:32

Robert Kock · Answer 2 · 2018-02-22T15:34:28.873

0

You might try this:

SELECT cod_proiect,
       MAX(data)
FROM
(
  SELECT DISTINCT
         ep.cod_proiect,
         ep.data
  FROM   ExpPoz   ep
  JOIN   Proiecte pr
    ON   ep.cod_proiect = pr.cod_proiect
   AND   pr.stare = 'I'
)
GROUP BY cod_proiect;

edited Feb 22 '18 at 15:34

answered Feb 22 '18 at 15:25

Robert Kock

5,795
1
12
20

Get max date for each line in a table in SQL

2 Answers2