Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL max problem with multiple fields

Posted on 2010-09-02
10
360 Views
Last Modified: 2013-12-07
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!
0
Comment
Question by:umrminer
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33587702
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
 

Author Comment

by:umrminer
ID: 33587747
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
 
LVL 4

Expert Comment

by:tctekkie
ID: 33587856
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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 42

Expert Comment

by:dqmq
ID: 33587867
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
 
LVL 34

Expert Comment

by:johnsone
ID: 33587910
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
 

Author Comment

by:umrminer
ID: 33588429
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
 
LVL 34

Accepted Solution

by:
johnsone earned 225 total points
ID: 33588612
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
 
LVL 32

Expert Comment

by:awking00
ID: 33604267
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
 
LVL 32

Expert Comment

by:awking00
ID: 33604290
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 25 total points
ID: 33609759
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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question