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!
umrminerAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
I believe this should work
SELECT DATE, 
       HOUR, 
       name, 
       load, 
       version, 
FROM   (SELECT DATE, 
               HOUR, 
               name, 
               load, 
               version, 
               Row_number() OVER (PARTITION BY DATE, HOUR, name ORDER BY version 
               DESC) 
                      rn 
        FROM   yourtable t 
        WHERE  name IN ( 'Jim', 'Bob' ) 
               AND DATE >= To_date('01-jan-09', 'dd-mon-yy') 
               AND DATE <= To_date('02-jan-09', 'dd-mon-yy')) t2 
WHERE  rn = 1 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
umrminerAuthor Commented:
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?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tctekkieCommented:
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
0
 
dqmqCommented:
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
0
 
johnsoneSenior Oracle DBACommented:
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.
0
 
umrminerAuthor Commented:
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)
0
 
awking00Commented:
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).
0
 
awking00Commented:
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')
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
if you want to filter the results which are already given by the query "AngelIII" then use something like this..

select *
from ( select *
 from (select t.*
         , row_number() over (partition by name, date, hour desc) rn
       from yourtable t
 ) where t.rn = 1 )
where <<date_col_condition>> --> use date field name and logical operator with values
and name in ('ABC','DEF') ; --> change values here accordingly to jim, bob , etc..

If you want to do filter before that query runs, then

select *
 from (select t.*
         , row_number() over (partition by name, date, hour desc) rn
       from yourtable t
where <<date_col_condition>> --> you can give any valid date condition here with your date field
and name in ('ABC','DEF') --> change values here accordingly to jim, bob , etc..
 ) where t.rn = 1 ;

But please note that the results may or may not be the same depending on what conditions you put in there in the where clause.

Thanks,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.