two dimentional 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,
Software/Hardware used:
Software/Hardware used:
ASKED:
February 17, 2009 5:53 PM
UPDATED:
February 3, 2010 3:47 PM
Answer Wiki:
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
To see all answers submitted to the Answer Wiki: View Answer History.
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.
Good stuff, BigKat. Really enjoyed that and built a working example over my database. I will certainly use this tip. Thanks.