Solved

SQL max problem with multiple fields

Posted on 2010-09-02
10
355 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 142

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

770 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