CODEWITHSUNDEEP
sqlt-sqlgroup-bysql-order-by
Murtaza Mandvi
Murtaza Mandvi

Reputation: 10998

SQL Executing Order by and Group by together

I have the following table 

UNIQUEKEY    ID      Clicks IsProcessed   INSERTDATE
 1         100001   10        1         2011-05-14 00:00:00.000
 2         100001   20        0         2011-05-13 00:00:00.000
 3         100001   30        1         2011-05-18 00:00:00.000
 4         100002   10        1         2011-05-20 00:00:00.000
 5         100002   15        0         2011-05-24 00:00:00.000
 6         100002   10        0         2011-05-05 00:00:00.000

I need a T-SQL query which will first Order by INSERTDATE (desc) Once ordered it should return me a GROUP by on ID and IsProcessed and the result should display ALL columns Orderby and grouped by as described above.

EDIT : So If we run the query for lets say ID 10001 it should give me 2 result sets : Desired Result :

UNIQUEKEY    ID      Clicks IsProcessed   INSERTDATE
 3         100001   30        1         2011-05-18 00:00:00.000
 1         100001   10        1         2011-05-14 00:00:00.000        
 2         100001   20        0         2011-05-13 00:00:00.000

Reson being ID and IsProcessed are serving as a key and the Result is sorted by Date (desc).

Any help would be appreciated !

Upvotes: 0

Views: 893

Answers (1)

Gábor Plesz
Gábor Plesz

Reputation: 1223

Try this:

declare @ID int = 100001
declare @IsProcessed int

declare
  crsMy cursor fast_forward for
select
  distinct IsProcessed
from
  tTest
order by
  IsProcessed desc

open crsMy

fetch next from crsMy into @IsProcessed

while @@fetch_status = 0
begin
  select
    UNIQUEKEY
    ,ID
    ,Clicks
    ,IsProcessed
    ,INSERTDATE
  from
    tTest
  where
    (ID=@ID)
    and
    (IsProcessed=@IsProcessed)
  order by
    INSERTDATE desc



  fetch next from crsMy into @IsProcessed
end

close crsMy
deallocate crsMy

or on sql fiddle

(on sqlfiddle is only the first result set displayed.)

Upvotes: 3

Related Questions