Select By Groups - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select By Groups

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-06-06 : 09:00:34

Hi,

I have the below table
JobID SeqID GroupID Contents
1 1 1 Load
1 2 1 CallRobot
1 3 1 Pick
1 4 2 Place
1 5 2 CallRobot
1 6 2 Load
1 7 3 Unload
1 8 3 Pick
1 9 3 Place
1 10 3 Load

Would Like to retrieve Maximum of Only 8 Rows at a time and Should select the complete GroupID

In the above selection I need to get
JobID SeqID GroupID Contents
1 1 1 Load
1 2 1 CallRobot
1 3 1 Pick
1 4 2 Place
1 5 2 CallRobot
1 6 2 Load

and NOT
JobID SeqID GroupID Contents
1 1 1 Load
1 2 1 CallRobot
1 3 1 Pick
1 4 2 Place
1 5 2 CallRobot
1 6 2 Load
1 7 3 Unload
1 8 3 Pick

Any suggestions?

Regards

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-06 : 09:08:28

what happens when a group contains more than 8 rows? Do you want just the first 8 or complete group no matter how many or never select that group?

Be One with the Optimizer
TG

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-06-06 : 09:10:15

It Should always be 8 or Less and never More

Thanks,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-06 : 09:21:58

You didn't fully answer the question:

>> Do you want just the first 8 or complete group no matter how many or never select that group?

EDIT:
sorry its just that your requirements conflict:
-never more than 8 rows total
-always the complete group

EDIT: (again) :)
So in other words if groupid 1 has 9 members what should be returned?

Be One with the Optimizer
TG

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-06-06 : 09:32:23

It should still return only 6 Rows and the Maximum no of rows that can be retrieved is only 8 .
It should be 8 or less than 8 and never more.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-06-06 : 11:01:11

Can anyone help ? Needed urgently.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-06 : 13:01:58

Perhaps no one is helping because you still haven't clarified the requirements.

I asked: "if groupid 1 has 9 members what should be returned?"
you responded: "It should still return only 6 Rows"

which 6 should be returned and why not 8?

Let's try it this way. Given the following data which seqIDs do you want in the result set?


JobID 	SeqID 	GroupID	Contents

1 	1 	1 	Load
1 	2 	2 	CallRobot
1 	3 	2 	Pick
1 	4 	2 	Place
1 	5 	2 	CallRobot
1 	6 	2 	Load
1 	7 	2 	Unload
1 	8 	2 	Pick
1       9       2       something
1 	10 	3 	Place
1 	11 	3 	Load

if it is 1-8 then that would violate your requirement of "Should select the complete GroupID".
so should it just be seqIDs (1,10,11) ?

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 04:25:54

seems like what you need is this


SELECT t.*
FROM YourTable t
CROSS APPLY (SELECT COUNT(*) AS CummGroupCount
             FROM YourTable
             WHERE JobID=t.JobID
             AND GroupID<=t.GroupID) t1
WHERE CummGroupCount<=8

if you want number of rows to be determined dynamically, replace hardcoded value (8) in above query with a parameter.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-07 : 08:21:59

In my sample data your results will only return the first row. groupid 2 will result in CummGroupCount > 8 so nothing after that will be selected including groupid 3 which only has 2 members.

That may indeed be what the OP wants but I who knows - they seem to have given up their "urgent" need

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 13:39:34

quote:
Originally posted by TG

In my sample data your results will only return the first row. groupid 2 will result in CummGroupCount > 8 so nothing after that will be selected including groupid 3 which only has 2 members.

That may indeed be what the OP wants but I who knows - they seem to have given up their "urgent" need

Be One with the Optimizer
TG

sorry i was not referring to your sample data. i was looking into sample data op posted and also his o/p. i think seeing the o/p what he's expecting something like mine as solution

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources