Return Single Row for highest value

Howardw · #1 (**permalink**) 12-16-09, 11:52

DB2 9.5 LUW

I have a table with multiple entries for a user - i want to return a row for each user with latest date.

Example
user - 1000
Rows on table
user - 1000
start date - 2009-05-09
grade - 5
user - 1000
start date - 2009-04-01
grade - 4
user - 1000
start date - 2009-02-01
grade -3

I used max on the date but it did not work - nor did distinct

Help would be appreciated.

n_i · #2 (**permalink**) 12-16-09, 12:04

Quote:

Originally Posted by Howardw

I used max on the date but it did not work - nor did distinct

MAX() works for me just fine; if it did not for you - you must have used it incorrectly.

I hope my answer is as specific as your question.

dav1mo · #3 (**permalink**) 12-16-09, 12:14

Howard,
It is typically helpful to give what SQL you have tried, so we can show you what needs to be corrected to fit your needs. If it is a complex question then table layout and some sample data would, also, help.
As Nick states the MAX() function works perfectly in all cases, so there is obviously something wrong with the SQL that you wrote or the data involved.
Something like the following should do what you are after:
select user, max(date)
from table....
where...
group by user

Dave

Lenny77 · #4 (**permalink**) 12-16-09, 12:48

Quote:

Originally Posted by Howardw

DB2 9.5 LUW

I have a table with multiple entries for a user - i want to return a row for each user with latest date.

Example
user - 1000
Rows on table
user - 1000
start date - 2009-05-09
grade - 5
user - 1000
start date - 2009-04-01
grade - 4
user - 1000
start date - 2009-02-01
grade -3

I used max on the date but it did not work - nor did distinct

Help would be appreciated.

Code:

select t1.* from table1 t1
where start_date = (select max(t2.start_date) from table1 t2
                               where t2.user = t1.user )

What is easier then DB2 ?

Lenny

tonkuma · #5 (**permalink**) 12-16-09, 12:58

If you want a row for a specific user with latest date,
you can use "ORDER BY start_date DESC" and "FETCH FIRST 1 ROW ONLY".

Please see "Retrieving MAXimum row"
in "SQL on Fire! Part 1":
http://www.sirdug.org/downloads/SQLonFire_1_SirDUG.pdf

Lenny77 · #6 (**permalink**) 12-16-09, 13:01

Nothing is new under Moon !

Lenny

tonkuma · #7 (**permalink**) 12-16-09, 13:13

Updated: Added AS q for DB2 9.1 for LUW or earlier.

Sometimes, using OLAP specification is more efficient than using correlated scalar-fullselect(or self join) with MAX() function.

Code:

SELECT user , start_date , grade
  FROM (SELECT t.*
             , ROW_NUMBER()
                  OVER( PARTITION BY user
                            ORDER BY start_date DESC ) rn
          FROM table_sample t
       ) AS q
 WHERE rn = 1
;

Internet Services

Web Development

Digital Marketing

Consumer Tech