| Author |
Topic |
|
SerjeyP
Starting Member
2 Posts |
 Posted - 2014-03-12 : 07:53:48
|
| I need some ideas about how to make this query without cursorinput 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 - 2014-03-12 : 09:09:18
|
| 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? |
 |
|
|
SerjeyP
Starting Member
2 Posts |
Posted - 2014-03-12 : 10:57:49
|
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". |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2014-03-13 : 08:10:03
|
| 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.CheersMIK |
|
|
|
|
|
|
Difficult query