Group Fixed number of records without using Loops

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 2008 Forums

Transact-SQL (2008)

Group Fixed number of records without using Loops

Author

Topic

damon88
Starting Member

5 Posts

Posted - 2012-01-06 : 02:14:44

So here is the situation.

I need to create a query which can group a fix number of records, its not really grouping in terms of order rather, i want to create another column named group which contains same numeric value for records belonging to same group

eg
Sno Name
1 abc
2 def
3 ghi
4 jkl
_____________________________
after query is run

Sno Name Group
1 abc 2
2 def 2
3 ghi 1
4 jkl 1

I want to know if this can be done without using a while loop. I wrote a function which does this using a loop but still i want to know if a nested query can do the same..

Can anyone help.

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 02:30:57

The scenario you explained here is not understandable.

Based on which column, you want to group?

What is the value that Group column contains? is it like unique identifier for a group?

damon88
Starting Member

5 Posts

Posted - 2012-01-06 : 02:51:32

quote:
Originally posted by Arumugam

The scenario you explained here is not understandable.

Based on which column, you want to group?

What is the value that Group column contains? is it like unique identifier for a group?

Hope this info makes everythingg clear

* Sno is the primary key
* There is no criteria for grouping, it can be in any order. We only need to have a fixed number of records in one group.
* The value in Group Column can be any value , integer or string, but consistent. Like in my eg i have used numeric to show 2 groups. (1 & 2)

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 04:27:41

Please Try this Query and let me know if it works,

Select SNO, Name, (SNO - 1)/<n> as Group From <TableName>

Replace your table name and 'n' represents the no. of records you want in a group.

Arumugam
Starting Member

11 Posts

Posted - 2012-01-06 : 04:32:12

Complete Solution,

Create Table #Table
(
SNo int,
Name varchar(10)
)
Insert Into #Table values(1,'abc')
Insert Into #Table values(2,'def')
Insert Into #Table values(3,'ghi')
Insert Into #Table values(4,'jkl')

Select SNo, Name, (SNo - 1)/2 + 1 AS GroupName From #Table
Drop Table #Table

Output
======
SNo Name GroupName
1 abc 1
2 def 1
3 ghi 2
4 jkl 2

damon88
Starting Member

5 Posts

Posted - 2012-01-06 : 04:41:59

quote:
Originally posted by Arumugam

Please Try this Query and let me know if it works,

Select SNO, Name, (SNO - 1)/<n> as Group From <TableName>

Replace your table name and 'n' represents the no. of records you want in a group.

Thanks a lot
It works :)

i really appreciate it. :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources