Selecting TOP 3 without a WHILE LOOP

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)

Selecting TOP 3 without a WHILE LOOP

Author

Topic

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-10-10 : 17:53:47

Guys,

I have a table A with 2 columns, both VARCHAR(255). The same word in the first column can be mapped to multiple different words in the 2nd column of table A. What I would like in the output are only TOP 3 such words from column 2 for each DISTINCT word in column one. TOP is defined by the alphabetic order. Below are the input and desired output examples:

Input table

sss b
sss d
sss c
sss b
sss e
def r
def s
xyz n
xyz t
xyz l

Desired output

sss a
sss b
sss c
def r
def s
xyz l
xyz n
xyz t

I know how to solve it using the WHILE loop, in a long way, but was wondering if someone can suggest an efficient SET BASED approach (i.e. a query) to achieve that.

Thanks a lot!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-10 : 18:17:55

[code]
DECLARE @t table (c1 char(3), c2 char(1))

INSERT INTO @t (c1, c2)
SELECT 'sss', 'b' UNION ALL
SELECT 'sss', 'd' UNION ALL
SELECT 'sss', 'c' UNION ALL
SELECT 'sss', 'b' UNION ALL
SELECT 'sss', 'e' UNION ALL
SELECT 'def', 'r' UNION ALL
SELECT 'def', 's' UNION ALL
SELECT 'xyz', 'n' UNION ALL
SELECT 'xyz', 't' UNION ALL
SELECT 'xyz', 'l'

SELECT c1, c2
FROM @t t1
WHERE (SELECT COUNT(DISTINCT c2) FROM @t WHERE c1 = t1.c1 AND c2 >= t1.c2) <= 3
ORDER BY c1, c2
[/code]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-11 : 10:53:23

quote:
Originally posted by sql_er

Guys,

I have a table A with 2 columns, both VARCHAR(255). The same word in the first column can be mapped to multiple different words in the 2nd column of table A. What I would like in the output are only TOP 3 such words from column 2 for each DISTINCT word in column one. TOP is defined by the alphabetic order. Below are the input and desired output examples:

Input table

sss b
sss d
sss c
sss b
sss e
def r
def s
xyz n
xyz t
xyz l

Desired output

sss a
sss b
sss c
def r
def s
xyz l
xyz n
xyz t

I know how to solve it using the WHILE loop, in a long way, but was wondering if someone can suggest an efficient SET BASED approach (i.e. a query) to achieve that.

Thanks a lot!

Alternative solution
declare @t table(longWord varchar(50), letter varchar(50))

insert @t
select 'sss', 'b'
union all select 'sss', 'd'
union all select 'sss', 'c'
union all select 'sss', 'e'
union all select 'sss', 'a'
union all select 'def', 'r'
union all select 'def', 's'
union all select 'xyz', 'n'
union all select 'xyz', 't'
union all select 'xyz', 'l'
union all select 'xyz', 'w'

select longWord, letter
FROM @t t1
WHERE t1.letter in
(
select top 3 letter from @t
WHERE longWord=t1.longWord
order by longWord, letter
)
order by longword,letter

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-10-11 : 11:42:39

Thanks a lot folks!

The solutions were great!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-11 : 15:23:31

Here is another less efficient way to do it. :)


SELECT 
	a.c1, 
	a.c2
FROM 
	@t a
INNER JOIN
	@t b
	ON a.c1 = b.c1
WHERE 
	a.c2 >= b.c2
GROUP BY 
	a.c1, 
	a.c2 
HAVING 
	COUNT(DISTINCT b.c2) BETWEEN 1 AND 3
ORDER BY 1 ASC

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 2007-10-15 : 13:45:56

This one works well as well.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources