QUERY - 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

General SQL Server Forums

New to SQL Server Programming

QUERY

Author

Topic

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 05:52:48

Hi

I have 24904 records in a table .
i want to split each 12 records with series of coreesponding ROW_ID

Pls help

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-10-23 : 05:56:47

We need sql server version (2000, 2005, 2008), table structure and some sample data and your wanted output in relation to the given sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

WoodHouse
Posting Yak Master

211 Posts

Posted - 2009-10-23 : 06:00:19

hi

SQL SERVER 2005
I need ID column like this

1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12
its upto 24904
but there no option to use RANK() OR Dense_Rank()..
with using these function i need to create each 12 groups

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-23 : 06:37:55

hi

Try this...

SELECT A.ID%12+1
(
select row_number() over(order by COL)as ID FROM <TABLE>)as A

-------------------------
R...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-23 : 06:49:38

Slightly changed!!

SELECT coalesce(nullif((A.ID %12),0),12)
from
(select row_number() over(order by COL)as ID FROM <TABLE>))as A

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-23 : 06:59:24

Try This toooo..

select coalesce(nullif(row_number() over (order by column_name)%12,0),12)
from <table_name>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-23 : 08:06:30

quote:
Originally posted by senthil_nagore

Try This toooo..

select coalesce(nullif(row_number() over (order by column_name)%12,0),12)
from <table_name>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Hi woodhouse & senthil

Best one try this.....

Total Records 24904


SELECT DENSE_RANK() OVER (PARTITION BY B.ID ORDER BY B.COL2) as ID FROM 
(
SELECT NTILE(2076) OVER ( ORDER BY COL1 DESC)AS ID,*  FROM <TABLE_NAME>
)as B

-------------------------
R...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-23 : 08:30:13

quote:
Originally posted by rajdaksha

quote:
Originally posted by senthil_nagore

Try This toooo..

select coalesce(nullif(row_number() over (order by column_name)%12,0),12)
from <table_name>

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Hi woodhouse & senthil

Best one try this.....

Total Records 24904

SELECT DENSE_RANK() OVER (PARTITION BY B.ID ORDER BY B.COL2) as ID FROM
(
SELECT NTILE(2076) OVER ( ORDER BY COL1 DESC)AS ID,* FROM <TABLE_NAME>
)as B

-------------------------
R...

Hi rajdaksha,

B.COL2 referes??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-10-23 : 09:09:36

[code]SELECT 1 + recID % 12 AS recID,
*
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY Co1l) - 1 AS recID,
*
FROM Table1
) AS d[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources