Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

select row with max of two columns

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
What we learned in Webroot's webinar on multi-vector protection.
This video shows how to recover a database from a user managed backup
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…

722 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