CODEWITHSUNDEEP
sqlsql-serverselect
mhn
mhn

Reputation: 2750

find latest value across rows for columns

I have a table with data as below.

compid  manualscandate  staticscandate  dynscandate
1       1-Aug-12    3-Aug-12    2-Aug-13
1       2-Aug-12    2-Aug-12    3-Aug-12
1       3-Aug-12    1-Aug-12    1-Aug-12
2       1-May-12    3-May-12    2-May-13
2       2-May-12    2-May-12    3-May-12
2       3-May-12    1-May-12    1-May-12

i need to find the latest dates for each compid.. such that .. the output table is 

compid  manualscandate  staticscandate  dynscandate
1       3-Aug-12    3-Aug-12    3-Aug-13
2       3-May-12    3-May-12    3-May-12

what is the best way to get this

Upvotes: 0

Views: 36

Answers (1)

juergen d
juergen d

Reputation: 204924

If you group around the compid then you can use aggregate functions like max() to collect the data you want

select compid, 
       max(manualscandate) as manualscandate, 
       max(staticscandate) as staticscandate, 
       max(dynscandate) as dynscandate
from your_table
group by compid

Upvotes: 3

Related Questions