Solved

select row with max of two columns

Posted on 2009-05-19
4
437 Views
Last Modified: 2012-05-07
Table : THE_TABLE
Columns:
THE_TABLE_ID int
DATE_ID date_id  - (day since Jan 1 1970)
TIME_ID time_id - subset of DATE_ID  (the minute of the day (1 to 60 minutes *24))
FACT number

How do I select the fact value of the row with the highest DATE_ID and TIME_ID? TIME_ID and DATE_ID is indexes, so I do not want to use a function. I need this to work on DB2, MSSQL,. and Oracle.

This code examples works and using the indexes,. but I was wondering if there is a better way to write this that does not adversely effect performance.

CONSTRAINTS:
SQL must work in Oracle, DB2, and MSSQL
SQL must use the indexes on DATE_ID and TIME_ID columns.
SELECT *

  FROM the_table

 WHERE the_table_id =

          (SELECT MAX (f1.the_table_id)

             FROM the_table f1

            WHERE f1.date_id = (SELECT MAX (f3.date_id) maxdate

                                  FROM the_table f3)

              AND f1.time_id =

                           (SELECT MAX (f2.time_id) maxdatetime

                              FROM the_table f2

                             WHERE date_id = (SELECT MAX (f3.date_id) maxdate

                                                FROM the_table f3)))

Open in new window

0
Comment
Question by:mbevilacqua
4 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24426695
>SQL must work in Oracle, DB2, and MSSQL
big requirement. and your code is the only way to avoid any specifics ...

you might try this, sql 2005+, oracle 9+ should do it, not sure about db2 thoug:
SELECT *
  FROM (SELECT t.*
            , ROW_NUMBER() OVER (ORDER BY DATE_ID DESC, TIME_ID DESC) r
         FROM the_table t
      ) sq
WHERE sq.r = 1

Open in new window

0
 
LVL 6

Expert Comment

by:tangchunfeng
ID: 24428169
angelIII: your sql works on DB2 v8
0
 
LVL 1

Expert Comment

by:rbodepudi
ID: 24441088
I don't know about DB2, but, you can try this one on Oracle. Since you have indxes, it should return the result pretty quickly

SELECT FACT FROM the_table order by DATE_ID, TIME_ID desc where rownum < 2.
0
 

Author Closing Comment

by:mbevilacqua
ID: 31583246
You rock!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

760 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