Looking for SQL Statement

Tags:
SQL
Hello to all, I have following problem. I have a Table containing 2 Fields: Number and Date. The content could be following: Number Date 1 26.11.2006 3 29.11.2006 3 23.11.2006 4 25.11.2006 Now I want to select all numbers, but when there is a double (or more) Value (like 3), I would like to have the one with the youngest Date. So the Result of the Query should be: Number Date 1 26.11.2006 3 23.11.2006 4 25.11.2006 I just can't figure out how to do this with the SELECT-Statement. Would be very glad for any solutions. Thanks, Chris
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

It seems to me as simple as

select number, min(date)
from table
group by number;

Or am I missing something?

Best regards
Christian

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Kurthp
    Try the following: select mynumber, min(trunc(mydate)) min_date from chris group by mynumber; CREATE TABLE CHRIS ( MYNUMBER NUMBER, MYDATE DATE )
    195 pointsBadges:
    report
  • Pratul
    Hi Chris, Let's suppose you have two columns col1 & col2. col1 is number and col2 is date. Select Query: select col1,MIN(col2) from table_name GROUP BY col1; Thanks, Pratul
    0 pointsBadges:
    report
  • Gristle
    If you want to select the row with the earliest date (suppose there were other columns you wanted to display in results) then in DB2 SQL you can use the ROWNUMBER function... SELECT NUMBER ,DATE ,OTHERCOLUMN FROM (SELECT NUMBER ,DATE ,OTHERCOLUMN ,ROWNUMBER() OVER (PARTITION BY NUMBER ORDER BY DATE) RN FROM YOURTABLE) ENUM WHERE RN = 1 Hope that is of use! Russell
    0 pointsBadges:
    report
  • Shekarraj
    Hi Try this. select mynumber , mydate from chris a where mydate in (select min(mydate) from chris b where a.mynumber = b.mynumber)
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: