Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
udayfn12
Starting Member
15 Posts |
 Posted - 2003-09-02 : 15:01:09
|
| Could any one help me on this. I need to get the top 4 (on Date ) rows into a single row.CREATE TABLE RATINGS (CODE VARCHAR(5), RATING INT, RATING_DATE DATETIME)INSERT INTO RATINGS VALUES ('A',1,'1998-02-08')INSERT INTO RATINGS VALUES ('A',1,'2003-09-02')INSERT INTO RATINGS VALUES ('A',5,'2003-07-08')INSERT INTO RATINGS VALUES ('A',2,'1999-11-04')INSERT INTO RATINGS VALUES ('A',2,'2003-09-28')INSERT INTO RATINGS VALUES ('A',1,'2002-08-28')INSERT INTO RATINGS VALUES ('A',5,'2000-02-12')INSERT INTO RATINGS VALUES ('A',8,'1999-05-04')INSERT INTO RATINGS VALUES ('A',5,'2003-07-19')INSERT INTO RATINGS VALUES ('B',1,'1998-02-08')INSERT INTO RATINGS VALUES ('B',3,'2003-07-12')INSERT INTO RATINGS VALUES ('B',5,'2003-05-28')Query :SELECT TOP 4 RATING, RATING_DATE FROM RATINGS WHERE CODE = 'A' ORDER BY RATING_DATE DESCThanks in advance,Reddy. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-09-02 : 15:03:36
|
| You could use Jay's solution which puts them into CSV form:[url]http://www.sqlteam.com/item.asp?ItemID=11021[/url]Tara |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-09-03 : 03:12:44
|
Perhaps you are looking for something like this:SELECT 'A' AS 'CODE', MAX(CASE RANK WHEN 1 THEN RATING_DATE ELSE NULL END) AS 'First',MAX(CASE RANK WHEN 2 THEN RATING_DATE ELSE NULL END) AS 'Second',MAX(CASE RANK WHEN 3 THEN RATING_DATE ELSE NULL END) AS 'Third',MAX(CASE RANK WHEN 4 THEN RATING_DATE ELSE NULL END) AS 'Fourth'FROM( SELECT TOP 4 RATING, RATING_DATE, (SELECT COUNT(*) FROM RATINGS I WHERE CODE = 'A' AND I.RATING_DATE > O.RATING_DATE) + 1 AS RANK FROM RATINGS O WHERE CODE = 'A' ORDER BY RATING_DATE DESC) Foo Owais
Make it idiot proof and someone will make a better idiot |
|
|
|
|
|
|
|
|
Top 4 rows into a single row