N records for each value - 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 2000 Forums

Transact-SQL (2000)

N records for each value

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-02-17 : 11:52:59

I am trying to select 3 records for each value in a column, but the best I have been able to return is the records that have values that have 3 or less records in the whole file.

select rec_id, ValueA from
(
select *,
(select count(*) from TableA b where a.ValueA = b.ValueA) as counter
from TableA a
) subq
where counter < 4
and rec_id in (select rec_id from TableA)

This is returning:
1 A
2 A
3 A
4 B
5 B
6 C
11 E
12 E
13 E
14 F

But what I want it to return is:
1 A
2 A
3 A
4 B
5 B
6 C
7 D
8 D
9 E
11 E
12 E
13 E
14 F
15 G
16 G
17 G

Thanks for any help you can give.

------------------------------------------
CREATE TABLE [dbo].[TableA] (
[rec_id] [int] IDENTITY (1, 1) NOT NULL ,
[ValueA] [char] (10) NULL
)
go

Insert TableA (ValueA) values ('A')
Insert TableA (ValueA) values ('A')
Insert TableA (ValueA) values ('A')
Insert TableA (ValueA) values ('B')
Insert TableA (ValueA) values ('B')
Insert TableA (ValueA) values ('C')
Insert TableA (ValueA) values ('D')
Insert TableA (ValueA) values ('D')
Insert TableA (ValueA) values ('D')
Insert TableA (ValueA) values ('D')
Insert TableA (ValueA) values ('E')
Insert TableA (ValueA) values ('E')
Insert TableA (ValueA) values ('E')
Insert TableA (ValueA) values ('F')
Insert TableA (ValueA) values ('G')
Insert TableA (ValueA) values ('G')
Insert TableA (ValueA) values ('G')
Insert TableA (ValueA) values ('G')
go

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-17 : 11:58:23

try:


Select rec_id, ValueA
From TableA as A
Where (Select count(*) From TableA Where rec_id<=A.rec_id and ValueA = A.ValueA)<=3

Go with the flow & have fun! Else fight the flow

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2005-02-17 : 11:59:45

that worked perfectly - thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources