5 rows for each city - 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)

5 rows for each city

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2007-12-13 : 03:28:27

Hi

I have a table called mycustomers.
Table columns are custName,city.

I want to display 5 rows for each city by using single select query

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-13 : 05:02:27

CREATE TABLE #t
(
[Name] varchar(50),
City varchar(50)
)

INSERT INTO #t values ('Visakh','A' )
INSERT INTO #t values ('bbb','A' )
INSERT INTO #t values ('cc','A' )
INSERT INTO #t values ('sadqw','A' )
INSERT INTO #t values ('asvcr','A' )
INSERT INTO #t values ('rhj7uy','A' )
INSERT INTO #t values ('dfhj6yu','A' )
INSERT INTO #t values ('svrtvb','B' )
INSERT INTO #t values ('eferewgvb','B' )
INSERT INTO #t values ('devrtbv','B' )
INSERT INTO #t values ('cc','B' )
INSERT INTO #t values ('sadqw','B' )
INSERT INTO #t values ('asvcr','C' )
INSERT INTO #t values ('rhj7uy','C' )
INSERT INTO #t values ('dfhj6yu','C' )
INSERT INTO #t values ('dfbh5y','C' )
INSERT INTO #t values ('Visakh','B' )
INSERT INTO #t values ('bbb','C' )
INSERT INTO #t values ('cc','C' )
INSERT INTO #t values ('sadqw','C' )
INSERT INTO #t values ('asvcr','B' )
INSERT INTO #t values ('rhj7uy','B' )
INSERT INTO #t values ('dfhj6yu','A' )
INSERT INTO #t values ('dfbh5y','C' )
;
WITH CTE (RowNo,City,Name)AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY City Order by [Name]) AS 'RowNo',
City,
[Name]
FROM #t
)

SELECT * FROM CTE where RowNo <6

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-13 : 06:14:33

Also see what you can do with row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2007-12-13 : 06:26:41

Thank you very much Visakh

quote:
Originally posted by visakh16

CREATE TABLE #t
(
[Name] varchar(50),
City varchar(50)
)

INSERT INTO #t values ('Visakh','A' )
INSERT INTO #t values ('bbb','A' )
INSERT INTO #t values ('cc','A' )
INSERT INTO #t values ('sadqw','A' )
INSERT INTO #t values ('asvcr','A' )
INSERT INTO #t values ('rhj7uy','A' )
INSERT INTO #t values ('dfhj6yu','A' )
INSERT INTO #t values ('svrtvb','B' )
INSERT INTO #t values ('eferewgvb','B' )
INSERT INTO #t values ('devrtbv','B' )
INSERT INTO #t values ('cc','B' )
INSERT INTO #t values ('sadqw','B' )
INSERT INTO #t values ('asvcr','C' )
INSERT INTO #t values ('rhj7uy','C' )
INSERT INTO #t values ('dfhj6yu','C' )
INSERT INTO #t values ('dfbh5y','C' )
INSERT INTO #t values ('Visakh','B' )
INSERT INTO #t values ('bbb','C' )
INSERT INTO #t values ('cc','C' )
INSERT INTO #t values ('sadqw','C' )
INSERT INTO #t values ('asvcr','B' )
INSERT INTO #t values ('rhj7uy','B' )
INSERT INTO #t values ('dfhj6yu','A' )
INSERT INTO #t values ('dfbh5y','C' )
;
WITH CTE (RowNo,City,Name)AS
(
SELECT ROW_NUMBER() OVER ( PARTITION BY City Order by [Name]) AS 'RowNo',
City,
[Name]
FROM #t
)

SELECT * FROM CTE where RowNo <6

ranganath
Posting Yak Master

209 Posts

Posted - 2007-12-13 : 07:02:07

Hi,

Try this Als0

Select name, city ,RowId
From (
Select name, city , ROW_NUMBER() OVER ( PARTITION BY City Order by [Name])as RowId From #T )d
where RowId < 6

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2007-12-13 : 09:14:54

Thank you Ranganath

quote:
Originally posted by ranganath

Hi,

Try this Als0

Select name, city ,RowId
From (
Select name, city , ROW_NUMBER() OVER ( PARTITION BY City Order by [Name])as RowId From #T )d
where RowId < 6

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2007-12-13 : 09:15:49

Thank you madhivanan

quote:
Originally posted by madhivanan

Also see what you can do with row_number() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources