How to query this? [modified]

[Arun Immanuel]

The table consists of 3 columns: ID,Date,Value. Sample Data:

ID Date Value
1 1/1/2004 243
2 1/1/2004 0
3 1/1/2004 32423

1 2/1/2004 2435
2 2/1/2004 545
3 2/1/2004 0

The above table holds transactions for last 3 months for each ID(from 1 to 1000). It will have records till yesterday. I need a query that return 5 rows for each ID having value >0. The result might look like: for ID=1:last 5 dates where value>0 for ID=2:last 5 dates where value>0 and so on. Thanks in advance. Or atleast is there any way to find the 2nd maximum along with group by. Or atleast is there any way to combine "top" along with group by. -- modified at 12:26 Saturday 19th May, 2007

Regards, Arun Kumar.A

[enjoycrack]

IMO, you can use GROUP BY...HAVING...you can get more info from SQL online book << >>

8x Solutions Ltd

[Arun Immanuel]

Thank you very much for your reply... But still I am not able to find the exact answer.

Regards, Arun Kumar.A

[enjoycrack]

What have you tried so far? try this...SELECT ... FROM...GROUP BY ID HAVING value > 0 << >>

8x Solutions Ltd

[Arun Immanuel]

At last, I have found it:

SELECT A.* FROM
(
SELECT B.ID, B.Date,
RANK() OVER (PARTITION BY B.ID ORDER BY B.Date DESC) AS RowRank
FROM tblName B WHERE
B.Value>0 AND Date<CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)
GROUP BY
B.ID, B.Date
) A WHERE RowRank<=5 ORDER BY ID

Regards, Arun Kumar.A

[faizankhan]

the best soultion is create a cursor and put cursor in stored procedure(SP) , finally call SP ------------------------------- declare @vdd bigint declare C1 cursor for Select distinct id from pdata open C1 drop table #temsales CREATE TABLE #temsales ( col1 bigint, col2 datetime, col3 bigint ) fetch next from c1 into @vdd while @@fetch_status=0 begin insert into #temsales select top 5 * from pdata where sales>0 and id =@vdd fetch next from c1 into @vdd end close c1 deallocate c1 select * from #temsales ;)