I wish I could rank... HELP!! - 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)

I wish I could rank... HELP!!

Author

Topic

codeable
Starting Member

3 Posts

Posted - 2006-07-20 : 13:16:08

I have been pounding my head for two days on this.. and can't figure it out! I tried to google it, but don't even know what to call it, so, here we go.. By the way: this needs to be in MS SQL 2000.

I'll simply the joins into one table for ease of understanding. The following is the "table" structure. Please note this is for demonstration purposes, and there aren't really letters and number, they are ident fields and misc data.

===================
Words
----------------------------------
Letter(FK) | Word
----------------------------------
A Alpha
A Apple
A Another
B Beta
C Charlie
C Candy
D Delicious
D Dandy
E Elephant
E Exactly
E Easy
===================

I would like a sql statement that returns the first from each letter, and then the second, etc etc.. till they are all gone. Using that table, I'd like to see:

Alpha
Beta
Charlie
Delicious
Elephant
Apple
Candy
Dandy
Exactly
Another
Easy

The key to this query is I have no idea how many letters there will be (or a max) and the word count for each letter is unpredictable.

Please SQL GURU's - tell me there is a set-way to do this!

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2006-07-20 : 14:16:28

I used your example for simplicity. Hopefully you'll be able to use something out of this for your app.

create table #tbl
(
letter char(1),
word varchar(20)
)

insert into #tbl values('A', 'Alpha')
insert into #tbl values('A', 'Apple')
insert into #tbl values('A', 'Another')
insert into #tbl values('B', 'Beta')
insert into #tbl values('C', 'Charlie')
insert into #tbl values('C', 'Candy')
insert into #tbl values('D', 'Delicious')
insert into #tbl values('D', 'Dandy')
insert into #tbl values('E', 'Elephant')
insert into #tbl values('E', 'Exactly')
insert into #tbl values('E', 'Easy')
go

declare @loopit bit
set @loopit = 1
declare @tmp2 table
(
letter char(1),
word varchar(20)
)
while (@loopit = 1)
begin
insert into @tmp2 (letter, word)
select min(letter), min(word)
from #tbl
group by letter

if @@ROWCOUNT > 0
begin
delete from #tbl
from @tmp2 a
where a.letter = #tbl.letter
and a.word = #tbl.word
end
else
begin
break
end
end

select * from @tmp2
go

drop table #tbl

NOTES:
1) Duplicate entries are eliminated. If that is not desired, a different approach is necessary

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-20 : 14:34:18

This is a pretty goofy query but this might work.


SELECT c.Word
FROM (SELECT a.Word,
             (SELECT COUNT(*) 
              FROM Table b 
              WHERE a.Letter = b.Letter
              AND   a.Word < b.Word) AS Count
      FROM   Table a) c
ORDER BY c.Count ASC, c.Word ASC

codeable
Starting Member

3 Posts

Posted - 2006-07-20 : 16:47:40

OMG - Love it! gonna apply the "effective" ranking trick now, and see if that works. If not (for ease of size) I'll try the first one... be back!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-20 : 17:05:10

quote:
Originally posted by DustinMichaels

This is a pretty goofy query but this might work.

Nice query!

Peter Larsson
Helsingborg, Sweden

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-07-20 : 17:38:57

quote:
Originally posted by Peso

quote:
Originally posted by DustinMichaels

This is a pretty goofy query but this might work.
Nice query!

Peter Larsson
Helsingborg, Sweden

Thanks.

codeable
Starting Member

3 Posts

Posted - 2006-07-20 : 19:56:04

YES! Works suitably. Had to make some tweaks since my table structure isn't so simple, but the virtual rank idea is b-e-a-utiful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources