SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difficult query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SerjeyP
Starting Member

2 Posts

Posted - 03/12/2014 :  07:53:48  Show Profile  Reply with Quote
I need some ideas about how to make this query without cursor

input data:

declare @t table(ID2 int identity(1,1), grOrder int , group varchar(10),ID int,cnt int)

insert into @t(grOrder,group,id,cnt)
values (1,'A',1,2)
,(2,'A',4,2)
,(3,'A',3,2)
,(4,'A',2,2)
,(4,'B',2,3)
,(2,'B',6,3)
,(5,'B',7,3)
,(3,'B',8,3)
,(1,'B',1,3)
,(1,'C',2,2)
,(3,'C',10,2)
,(2,'C',11,2)
,(4,'C',12,2);

output data:
declare @res table (id int ,group varchar(10))
insert into @res(id,group)
values (1,'A')
,(4,'A')
,(6,'B')
,(8,'B')
,(2,'B')
,(11,'C')
,(10,'C')
;
Algorithm info:
For every "Group" take first "Cnt" records unique by "ID" in order "grOrder", but for every last group (alphabetically order) we can't take records with "ID" which was taken for previous group.


Robowski
Posting Yak Master

101 Posts

Posted - 03/12/2014 :  09:09:18  Show Profile  Reply with Quote
Can't follow what you are asking sorry...

For every "Group" take first "Cnt" records unique by "ID" in order "grOrder",

So the first group is A and the first CNT record is 2 if you order by grOrder ASC?
Go to Top of Page

SerjeyP
Starting Member

2 Posts

Posted - 03/12/2014 :  10:57:49  Show Profile  Reply with Quote
quote:
Originally posted by Robowski

Can't follow what you are asking sorry...

For every "Group" take first "Cnt" records unique by "ID" in order "grOrder",

So the first group is A and the first CNT record is 2 if you order by grOrder ASC?


Yes, for the first group A needs take 2 first records in order by grOrder asc. For the second group B needs take 3 first records, except (by ID) records were taken on first step. The number of records for every group in resulting query sets in field "cnt".
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/13/2014 :  08:10:03  Show Profile  Reply with Quote
Still unclear. You may need to explain why do you want one A, three Bs and Two Cs record in the output as per given input.

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000