?
Solved

SQL max problem with multiple fields

Posted on 2010-09-02
10
Medium Priority
?
390 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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 35

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 35

Accepted Solution

by:
johnsone earned 900 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 100 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

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.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

809 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