Improve company productivity with a Business Account.Sign Up

x
?
Solved

select row with max of two columns

Posted on 2009-05-19
4
Medium Priority
?
451 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
I recently worked on a Wordpress site that utilized the popular ContactForm7 (https://contactform7.com/) plug-in that only sends an email and does not save data. The client wanted the data saved to a custom CRM database. This is my solution.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

607 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