Thread: Ranking?
Hi list,
Wondering if you could help me out here? I've got a table like this:
id name data
1 sully 567
2 sully 789
3 sully 456
4 denan 890
5 denan 678
now I want to have this:
id name data rank
1 sully 567 2
2 sully 789 1
3 sully 456 3
4 denan 890 1
5 denan 678 2
I'm think along the lines with count, something like this:
select id,name,data, (select count (names) from table as tmp where data.tmp > data.table) as rank
this (I know) is not correct. What would be?
Greetings sjors
On Thu, 25 Sep 2003 17:45:35 +0200, "Sjors" <jkwast@freeler.nl> wrote:
>now I want to have this:
>
>id name data rank
>1 sully 567 2
>2 sully 789 1
>3 sully 456 3
>4 denan 890 1
>5 denan 678 2
SELECT id, name, data, (SELECT count(*) + 1
FROM tbl AS tmp
WHERE tmp.name = tbl.name
AND tmp.data > tbl.data) AS rank
FROM tbl;
should get you going but might perform horribly without an index on
name ...
Servus
Manfred
Hi Manfred, Sometimes the solution is right in front of you, but the cloud is to thick. Thanks a lot, it worked like a charm. The reason I want this, is to have a new type of index, so speed is not a issue and data file not big (just about 15000 records). I know, it's sounds silly. Greetings Sjors > >now I want to have this: > > > >id name data rank > >1 sully 567 2 > >2 sully 789 1 > >3 sully 456 3 > >4 denan 890 1 > >5 denan 678 2 > > SELECT id, name, data, (SELECT count(*) + 1 > FROM tbl AS tmp > WHERE tmp.name = tbl.name > AND tmp.data > tbl.data) AS rank > FROM tbl; > > should get you going but might perform horribly without an index on > name ... > > Servus > Manfred >