How can I select 5 rows of each name

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

SQL Server Development (2000)

How can I select 5 rows of each name

Author

Topic

Kim
Starting Member

3 Posts

Posted - 2004-09-09 : 11:35:59

I have a table with more than 1 milion addresses. Every streetname exists many times but they all have different index. The table looks something like this.

INDEX STREET NUMBER
1 StreetA 2
2 StreetA 2
3 StreetA 5
4 StreetA 7
5 StreetA 8
.
.
.
11 StreetB 1
12 StreetB 1
13 StreetB 3
14 StreetB 4
15 StreetB 5
.
.
.
22 StreetC 1
23 StreetC 1
24 StreetC 1
25 StreetC 3
26 StreetC 5
.
.
.

I am trying to select 5 rows of each streetname but I don´t know how to do this.

Tanks / Kim

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-09 : 12:45:46

I don't know that i would do this on a million record table, but you could try...


Select * 
From myTable A
Where 5>(Select count(*) From myTable Where Street = A.street and id < A.id)

Corey

Kim
Starting Member

3 Posts

Posted - 2004-09-10 : 01:26:22

Clear and simple. Thank you Seventhnight.

>>>Kim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources