Solved

SQL max problem with multiple fields

Posted on 2010-09-02
10
333 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 31

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 31

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now