Solved

select row with max of two columns

Posted on 2009-05-19
4
444 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
[X]
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
4 Comments
 
LVL 143

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

624 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