asked on

SQL max problem with multiple fields

Hello experts...I am very new to SQL and need some help with the max function. I need to select the max "version" for each date, hour, and name from the following table and:

Date Hour Name Load Version
1/1/10 1 Bob 5 1
1/1/10 1 Bob 4 2
1/1/10 1 Bob 7 3
1/1/10 1 Jim 5 1
1/1/10 1 Jim 5 2
1/1/10 2 Bob 7 1
1/1/10 2 Bob 5 2
1/1/10 2 Bob 8 3
1/1/10 2 Bob 9 4
1/1/10 2 Jim 7 1

Based on the data above, the results would be:
Date Hour Name Load Version
1/1/10 1 Bob 7 3
1/1/10 2 Bob 9 4
1/1/10 1 Jim 5 2
1/1/10 2 Jim 7 1

I need to return 1 value for each date, hour, and name based on the MAX version. But, I also need it to return the associated "Load" for that row. Will the SQL syntax change if there more descriptive columns like "Load"?

Thanks for your help!

Guy Hengel [angelIII / a3]

something like this should do:

select * 
 from (select t.*
         , row_number() over (partition by name, date, hour desc) rn
       from yourtable t
 )
where t.rn = 1

Open in new window

umrminer

ASKER

angelIII - Thanks, but I forgot to mention that I am not selecting all of the columns in the table. I only want to display maybe 5 or 6 columns out of a total of more than 40. How would the syntax change?

tctekkie

This should work for you (of course change the table name to the actual table name):

SELECT Date,
      Hour,
      Name,
      MAX(Load),
      MAX(Version)
FROM tbl_usertable
GROUP BY Date, Hour, Name
ORDER BY Name, Hour

dqmq

Just name the columns you want to the first-line select. Also, I think the third line should be:

, row_number() over (partition by name, date, hour order by version desc) rn

johnsone

tcteckkie - that will only work if the max load always corresponds to the max version. Based on the sample data posted, that does not appear to always be the case.

umrminer - you can use angelIII's query. It is an example. Just replace the * with the column names that you want to select.

umrminer

ASKER

I need to include WHERE conditions on Name and Date as well. I used the following and get an ORA-00907: missing right parenthesis error. Any suggestions

SELECT
Date,
Hour,
Name,
Load,
Version,
FROM (SELECT t.Date, t.Hour, t.Name, t.Load, t.Version, row_number() over (partition by date, hour, name

WHERE
t.rn = 1 AND
Name in (Jim, Bob) AND
Date >= '01-jan-09' AND
Date <= '02-jan-09' AND
ROWNUM <= 50000

ORDER BY Version Desc) rn from yourtable t)

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awking00

What is the actual name and datatype of your date field? If it's a true date datatype, then johnsone's solution should work fine and allow you to select additional columns (which would have to be in both the main and subselect statements).

awking00

Also, if you are truly looking for January 1 only records and your datefield contains no time element, you would want to use "<" and not "<=" for the second date
"... AND DATE >= TO_DATE('20090101','YYYYMMDD') AND DATE < TO_DATE('20090102','YYYYMMDD')

SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial