how to Avoid cursor here

Hi,
AF in understand i did it as :

select top 2 volume, strttime,appid from testtime
order by volume,strttime desc


which returns
Volume StrtTime Appid
========================================
1025022005-04-12 00:00:00.0006022
109172005-04-12 01:00:00.0006061


Regards

Hemantgiri S. Goswami
ghemant@gmail.com
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami

set nocount on
use northwind
select
t1.CustomerID
, t1.OrderDate
from
orders t1
where
t1.OrderDate in
(
select top 2 --with ties
t2.OrderDate
from
orders t2
where
t2.CustomerID = t1.CustomerID
order by
t2.OrderDate desc
)
order by
t1.CustomerID
, t1.OrderDate desc
set nocount off


--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Try this also


Select appid, strttime, volume From yourTable T
Where (Select count(*) From yourTable Where strttime=A.strttime and appid <= A.appid)<=2
Order By strttime, appid Desc




Madhivanan

Failing to plan is Planning to fail

Wow, very good! Adapting your statement to the Northwind db results in following figures<br /><pre id="code"><font face="courier" size="2" id="code"><br />Table 'Orders'. Scan count 827, logical reads 269474, physical reads 2, read-ahead reads 3.<br /> <br />------------------------------------------------------ <br />1900-01-01 00:00:01.243<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />Second is execution time. Your statement<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.CustomerID , t1.OrderDate<br /> FROM Orders t1 <br /> WHERE <br /> (SELECT COUNT(*) <br /> FROM Orders <br /> WHERE OrderDate&lt;=t1.OrderDate AND CustomerID = t1.CustomerID) &lt;=2 <br /> ORDER BY t1.CustomerID, t1.OrderDate DESC<br /></font id="code"></pre id="code"><br />results in<br /><pre id="code"><font face="courier" size="2" id="code"><br />Table 'Worktable'. Scan count 823, logical reads 3351, physical reads 0, read-ahead reads 0.<br />Table 'Orders'. Scan count 2, logical reads 44, physical reads 3, read-ahead reads 12.<br /> <br />------------------------------------------------------ <br />1900-01-01 00:00:00.080<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />[<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Frank, May I know how you know that Statistics? Which one have I to set?

Madhivanan

Failing to plan is Planning to fail

SET STATISTICS IO ON
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DECLARE @dt_start1 DATETIME, @dt_start2 DATETIME
SET @dt_start1 = GETDATE()
SELECT t1.CustomerID, t1.OrderDate
FROM Orders t1
WHERE t1.OrderDate IN
(SELECT TOP 2 t2.OrderDate
FROM Orders t2
WHERE t2.CustomerID = t1.CustomerID
ORDER BY t2.OrderDate DESC)
ORDER BY t1.CustomerID, t1.OrderDate DESC
SELECT GETDATE()-@dt_Start1
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET @dt_Start2 = GETDATE()
SELECT t1.CustomerID , t1.OrderDate
FROM Orders t1
WHERE
(SELECT COUNT(*)
FROM Orders
WHERE OrderDate<=t1.OrderDate AND CustomerID = t1.CustomerID) <=2
ORDER BY t1.CustomerID, t1.OrderDate DESC
SELECT GETDATE()-@dt_Start2
SET STATISTICS IO OFF


--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Thats cool Frank<br /><br />One more doubt<br />Whenever I copy the code (written using

Code:

...) and paste it in the Query Analyser, all come as single line without line feed. Is there any trick to avoid this? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

That's pretty annoying, isn't it? [<img src='/community/emoticons/emotion-6.gif' alt='' />]<br />No, I haven't found a public workaround. But I use a private one. As moderator you can go and edit the posting which pops up the usual text area. The I copy the code from there to the clipboard and cancel the edit. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Hi,
actually thats what i wondering why you have ask this question but now its clear to me ! [:0][V]

quote:Originally posted by ranjitjain

Hi Hemant thanx for the try but dear ofcourse i dont need this simpler.
Sorry i dint posted the desired output.
I need the resultset as

6024 2005-04-12 00:00:00.000 27395404
6027 2005-04-12 00:00:00.000 122038
6061 2005-04-12 01:00:00.000 10917
6062 2005-04-12 01:00:00.000 30705
6024 2005-04-12 02:00:00.000 24757602
6027 2005-04-12 02:00:00.000 111234
6061 2005-04-12 03:00:00.000 48068
6063 2005-04-12 03:00:00.000 69897
6168 2005-04-12 04:00:00.000 7056692
6174 2005-04-12 04:00:00.000 25223703

Hemantgiri S. Goswami
ghemant@gmail.com
"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri Goswami

Too lazy today? [<img src='/community/emoticons/emotion-5.gif' alt='' />]<br />Is this better?<br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT t1.appid, t1.strtime, t1.volume<br /> FROM #t t1<br /> WHERE t1.volume IN<br /> (SELECT TOP 2 t2.volume<br /> FROM #t t2<br /> WHERE t2.strtime = t1.strtime<br /> ORDER BY t2.volume DESC)<br /> ORDER BY t1.strtime <br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

&gt;&gt;Yes i think i need a long weekend<br /><br />So you are doing heavy work<br />your boss must be proud of you [<img src='/community/emoticons/emotion-2.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail

If I understand this correctly now, one way might be to use a correlated subquery with the same IN() query, but use NOT IN() this. <br /><br />Enough for today, I'm going home now. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

Nah, was in a hurry yesterday when I typed this. Forget my posting. I would really consider using a report writer for this. Should be much easier. But in the meantime this should do:


SELECT appid, strtime, Report.Volume
FROM
(SELECT TOP 100 PERCENT LTRIM(t1.appid) AS appid, t1.strtime, t1.volume, 1 AS INNERGROUP
FROM #t t1
WHERE t1.volume IN
(SELECT TOP 2 t2.volume
FROM #t t2
WHERE t2.strtime = t1.strtime
ORDER BY t2.volume DESC)
ORDER BY t1.strtime
UNION ALL
SELECT 'Not included', t3.strtime, SUM(t3.volume), 2
FROM #t t3
WHERE t3.volume NOT IN
(SELECT TOP 2 t4.volume
FROM #t t4
WHERE t4.strtime = t3.strtime
ORDER BY t4.volume DESC)
GROUP BY strtime) Report
ORDER BY strtime, INNERGROUP


--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

A kludgy wordaround, but still nice. Thanks! [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />Thats cool Frank<br /><br />One more doubt<br />Whenever I copy the code (written using

Code:

...) and paste it in the Query Analyser, all come as single line without line feed. Is there any trick to avoid this? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Paste it into WordPad or MsWord first, then copy and paste it into QA.  WordPad will recognise the line feeds, and MSWord recognises the line feeds and the white space<br /><br />Cheers<br />Twan<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well. Thats nice<br />But other way is to click Reply with Quote Button and then copy the text and paste it in QA will work well<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail