Selecting rows per group - 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)

Selecting rows per group

Author

Topic

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-04 : 17:29:17

I'm just new here. Welcome to me..

Anyway, is it possible to query a rows base on group and base on quotient of rows?
QUOTIENT OF ROWS?

let me explain this

Example:
col1|col2
-----------
1 test --
1 test |
1 test > 5 rows/2(divisor) = 2 + remainder 1 = 3 rows
1 test |
1 test --
2 test --
2 test > 4 rows/2(divisor) = 2 + remainder 0 = 2 rows
2 test |
2 test --

this should be the result:
col1|col2
1 test --
1 test > this is the [b]3 rows[\b]
1 test --
2 test \
2 test / this is the [b]2 rows[\b]

And also this must be in a VIEW.
I am wondering if this is can be handled by ROW_NUMBER() function.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 18:37:43

[code]--Create TV
Declare @t table
(col1 int, col2 char(8))

--Prepare Sample Data
Insert @t
Select 1,'test' union all
Select 1,'test' union all
Select 1 ,'test' union all
Select 1 ,'test' union all
Select 1 ,'test' union all
Select 2 ,'test' union all
Select 2 ,'test' union all
Select 2 ,'test' union all
Select 2 ,'test'

--Roll SQL Wheel

Select col1,col2
from
(
Select col1,col2,Ntile(2)/*Since you have 2 groups */Over(Partition by col1 order by col1)as [Ntile]
from @t)z
Where [Ntile] = 1

--output
col1 col2
1 test
1 test
1 test
2 test
2 test [/code]

k_cire0426
Yak Posting Veteran

63 Posts

Posted - 2009-02-04 : 18:51:01

Hi thanks to you,

but what if you don't know exactly how may groups are there?
and the divisor is not actually the number of groups it could be 3 or 4 etc..it is regardless of how many groups does the table had.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 20:05:03

[code]--Create TV
Declare @t table
(col1 int, col2 char(8))

--Prepare Sample Data
Insert @t
Select 1,'test' union all
Select 1,'test' union all
Select 1 ,'test' union all
Select 1 ,'test' union all
Select 1 ,'test' union all
Select 2 ,'test' union all
Select 2 ,'test' union all
Select 2 ,'test' union all
Select 2 ,'test'

--Roll SQL Wheel

Declare @Z int
Set @Z =(Your Query to find number of groups)

Select col1,col2
from
(
Select col1,col2,Ntile(@Z) Over(Partition by col1 order by col1)as [Ntile]
from @t)z
Where [Ntile] = 1

--output
col1 col2
1 test
1 test
1 test
2 test
2 test [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources