Selecting the most updated record from each category/group of records

[TAAJ Singh]

Hi;
I have a situation where a query is returning multiple rows for each group.

For example :-

ID Groups Value Status Modified_Time
1 JAA 190 1 2011-10-14 12.01.23
2 JAA 4354 1 2011-5-23 11.01.23
3 JAA 343 1 2011-7-01 11.01.24
4 MTT 566 1 2011-10-01 12.01.25
5 YTT 333 1 2011-03-01 11.01.26
6 YTT 323 1 2011-10-01 11.01.27
7 SYY 1234 1 2011-10-01 11.01.28
8 SYY 336 1 2011-09-05 11.01.29
9 SYY 777 1 2011-01-01 12.01.30
10 SYY 554 1 2010-10-01 11.01.31
11 UTT 454 1 2011-10-01 11.01.32
12 UTT 332 1 2011-10-01 11.01.33
13 MPP 232 1 2011-10-01 11.01.34
14 JPP 231 1 2011-10-01 11.01.35

I want the most updated record from each group;
How to write DB2 SQL for this?

[yarbrough40]

not too familiar with DB2 but most db engines support the MAX function. try this

SELECT Groups, MAX(Modified_Time) AS Latest
FROM MyTable
GROUP BY Groups

If this works then you can modify the query to bring in the other fields using a subquery or temptables and joins.

[TAAJ Singh]

Thanks for your reply; Actually I want the most updated record based on the timestamp from each of the group;

[yarbrough40]

Ok, try "MIN" instead of "MAX" (I think it works that way for timestamps)

[Rabbit]

Do something like this

SELECT *
FROM someTable AS a
WHERE dateField = (
   SELECT MAX(dateField)
   FROM someTable AS b
   WHERE a.GroupField = b.GroupField
)

Alternatively, if you DB2 server supports the ROWNUMBER function, you can use that as well.

[TAAJ Singh]

Actually Its not happening; Can anyone help me on this ?

[yarbrough40]

I tried Rabbit's query and it works great. Where are you getting hung up?