two dimentional output

5 pts.
Tags:
SQL Query
SQL tables
Two-dimensional Output
I have a table. The structure looks like this: id(pk) year col1 col2 date 1 2008 50 60 feb09 2 2008 40 70 feb10 3 2009 60 80 feb23 4 2009 50 70 feb24 5 2008 40 80 feb26 I have no idea how to write a sql query to output a table which includes the latest of record of 2008, and the latest record of 2009. Can anyone help me out on this question? Thanks a lot,

Answer Wiki

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

If you are on SQL SERVER 2005 or above, you could use a query like this:

<pre> SELECT *
FROM
(SELECT id,year,col1,col2,ddate,
ROW_NUMBER() OVER (partition by year ORDER BY ddate DESC) rowNumber
FROM yourTable) ordered_temp
WHERE rowNumber = 1;</pre>

If you are not using SQL SERVER, or you are using an older version, please provide your DBMS and version number.
—————————————
Also works for DB2 for System i (is that the current name now :) ) at i6.1 (V6R1)

———–
or if you don’t have 2005

select t1.*
from your_table t1,
(select year, max(ddate) max_date from your_table group by year) t2
where t1.year = t2.year
and t1.ddate = t2.max_date

-Darryn

Discuss This Question: 2  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Pushkar099
    SELECT * FROM ( SELECT ROWNUM AS RANK, ID,YEAR, COL1,COL2,DATE FROM ( SELECT ROWNUM AS RANK,YEAR, ID,COL1,COL2,DATE FROM EMPLOYEE WHERE YEAR IN ('2008') ORDER BY DATE DESC ) A WHERE RANK=1 UNION SELECT ROWNUM AS RANK, ID,YEAR, COL1,COL2,DATE FROM ( SELECT ROWNUM AS RANK1, YEAR, COL1,COL2, DATE FROM EMPLOYEE WHERE YEAR IN ('2009') ORDER BY DATE DESC ) WHERE RANK=1 ) This particualr ansqwer is for Oracle database.
    10 pointsBadges:
    report
  • slateken
    Good stuff, BigKat. Really enjoyed that and built a working example over my database. I will certainly use this tip. Thanks.
    230 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following