Solved

select row with max of two columns

Posted on 2009-05-19
4
439 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

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

Suggested Solutions

Title # Comments Views Activity
SQL: launch actions one before the other 10 23
Access Crosstab Query with Multiple Values 4 32
sql server computed columns 11 31
SQL Server 2012 r2 - Sum totals 2 25
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.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

773 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