Thread: Rank
Dear All,
I Have below table
id | site_name | point
----+-----------+-------
1 | Site A | 40
2 | Site B | 90
3 | Site D | 22
4 | Site X | 98
----+-----------+-------
1 | Site A | 40
2 | Site B | 90
3 | Site D | 22
4 | Site X | 98
Would like to calc that Rank for each site, and look like
id | site_name | point | rank
----+-----------+-------+------
1 | Site A | 40 | 3
2 | Site B | 90 | 2
3 | Site D | 22 | 4
4 | Site X | 98 | 1
----+-----------+-------+------
1 | Site A | 40 | 3
2 | Site B | 90 | 2
3 | Site D | 22 | 4
4 | Site X | 98 | 1
What can I do to get result like that
On Sun, 2 May 2004 02:22:37 +0800 "Muhyiddin A.M Hayat" <middink@indo.net.id> threw this fish to the penguins: > I Have below table > > id | site_name | point > ----+-----------+------- > 1 | Site A | 40 > 2 | Site B | 90 > 3 | Site D | 22 > 4 | Site X | 98 > > Would like to calc that Rank for each site, and look like > > id | site_name | point | rank > ----+-----------+-------+------ > 1 | Site A | 40 | 3 > 2 | Site B | 90 | 2 > 3 | Site D | 22 | 4 > 4 | Site X | 98 | 1 Well, a simple minded solution would be: select id,site_name,point,(select count(*)from mytable t2 where t2.point >= t1.point) as rank from mytable t1; id | site_name | point | rank ----+-----------+-------+------ 4 | Site X | 98 | 1 2 | Site B | 90 | 2 1 | Site A | 40 | 3 3| Site D | 22 | 4 (4 rows) If mytable is huge this may be prohibitively slow, but it's worth a try. There's probably a self join that would be faster. Hmm... in fact: select t1.id,t1.site_name,t1.point,count(t2.point) as rank from mytable t1,mytable t2 where t2.point >=t1.point group byt1.id,t1.site_name,t1.point; id | site_name | point | rank ----+-----------+-------+------ 3 | Site D | 22 | 4 2 | Site B | 90 | 2 4 | Site X | 98 | 1 1| Site A | 40 | 3 (4 rows) -- George Young -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL)
Am 04.05.2004 16:11 schrieb george young: > On Sun, 2 May 2004 02:22:37 +0800 > "Muhyiddin A.M Hayat" <middink@indo.net.id> threw this fish to the penguins: > >>I Have below table >> >> id | site_name | point >>----+-----------+------- >> 1 | Site A | 40 >> 2 | Site B | 90 >> 3 | Site D | 22 >> 4 | Site X | 98 >> >> Would like to calc that Rank for each site, and look like >> >> id | site_name | point | rank >>----+-----------+-------+------ >> 1 | Site A | 40 | 3 >> 2 | Site B | 90 | 2 >> 3 | Site D | 22 | 4 >> 4 | Site X | 98 | 1 > > > Well, a simple minded solution would be: > > select id,site_name,point,(select count(*)from mytable t2 > where t2.point >= t1.point) as rank from mytable t1; > > id | site_name | point | rank > ----+-----------+-------+------ > 4 | Site X | 98 | 1 > 2 | Site B | 90 | 2 > 1 | Site A | 40 | 3 > 3 | Site D | 22 | 4 > (4 rows) > > If mytable is huge this may be prohibitively slow, but it's worth a try. > There's probably a self join that would be faster. Hmm... in fact: > > select t1.id,t1.site_name,t1.point,count(t2.point) as rank from mytable t1,mytable t2 > where t2.point >=t1.point group by t1.id,t1.site_name,t1.point; > > id | site_name | point | rank > ----+-----------+-------+------ > 3 | Site D | 22 | 4 > 2 | Site B | 90 | 2 > 4 | Site X | 98 | 1 > 1 | Site A | 40 | 3 > (4 rows) > > > -- George Young Another possibilty is to use a sequence: demo=# create temporary sequence ranking; demo=# select *,nextval('ranking') as rank from yourTable order by site_name asc; Greetins, Martin -- Martin Knipper www : http://www.mk-os.de Mail : knipper@mk-os.de Random Signature: ----------------- while( !asleep() ) sheep++;
Martin Knipper wrote: > Am 04.05.2004 16:11 schrieb george young: > >>On Sun, 2 May 2004 02:22:37 +0800 >>"Muhyiddin A.M Hayat" <middink@indo.net.id> threw this fish to the penguins: >> >> >>>I Have below table >>> >>>id | site_name | point >>>----+-----------+------- >>> 1 | Site A | 40 >>> 2 | Site B | 90 >>> 3 | Site D | 22 >>> 4 | Site X | 98 >>> >>>Would like to calc that Rank for each site, and look like >>> >>>id | site_name | point | rank >>>----+-----------+-------+------ >>> 1 | Site A | 40 | 3 >>> 2 | Site B | 90 | 2 >>> 3 | Site D | 22 | 4 >>> 4 | Site X | 98 | 1 >> >> >>Well, a simple minded solution would be: >> >>select id,site_name,point,(select count(*)from mytable t2 >> where t2.point >= t1.point) as rank from mytable t1; >> >> id | site_name | point | rank >>----+-----------+-------+------ >> 4 | Site X | 98 | 1 >> 2 | Site B | 90 | 2 >> 1 | Site A | 40 | 3 >> 3 | Site D | 22 | 4 >>(4 rows) >> >>If mytable is huge this may be prohibitively slow, but it's worth a try. >>There's probably a self join that would be faster. Hmm... in fact: >> >>select t1.id,t1.site_name,t1.point,count(t2.point) as rank from mytable t1,mytable t2 >> where t2.point >=t1.point group by t1.id,t1.site_name,t1.point; >> >> id | site_name | point | rank >>----+-----------+-------+------ >> 3 | Site D | 22 | 4 >> 2 | Site B | 90 | 2 >> 4 | Site X | 98 | 1 >> 1 | Site A | 40 | 3 >>(4 rows) >> >> >>-- George Young > > > Another possibilty is to use a sequence: > > demo=# create temporary sequence ranking; > demo=# select *,nextval('ranking') as rank from yourTable order by > site_name asc; > > Greetins, > Martin > wouldn't it have to be: select *, nextval('ranking') as rank from yourTable order by point desc; for the ranking to work?
Am 04.05.2004 18:47 schrieb Rob: > Martin Knipper wrote: >>Am 04.05.2004 16:11 schrieb george young: >>>On Sun, 2 May 2004 02:22:37 +0800 >>>"Muhyiddin A.M Hayat" <middink@indo.net.id> threw this fish to the penguins: >>> >>>>I Have below table >>>> >>>>id | site_name | point >>>>----+-----------+------- >>>>1 | Site A | 40 >>>>2 | Site B | 90 >>>>3 | Site D | 22 >>>>4 | Site X | 98 >>>> >>>>Would like to calc that Rank for each site, and look like >>>> >>>>id | site_name | point | rank >>>>----+-----------+-------+------ >>>>1 | Site A | 40 | 3 >>>>2 | Site B | 90 | 2 >>>>3 | Site D | 22 | 4 >>>>4 | Site X | 98 | 1 >>> >>> [...] >> >>Another possibilty is to use a sequence: >> >>demo=# create temporary sequence ranking; >>demo=# select *,nextval('ranking') as rank from yourTable order by >>site_name asc; >> > > wouldn't it have to be: > > select *, nextval('ranking') as rank > from yourTable > order by point desc; > > for the ranking to work? > Yes, you are right. I guess I was a little confused with the ordering in the example table Muhyiddin provided. Greetings, Martin -- Martin Knipper www : http://www.mk-os.de Mail : knipper@mk-os.de
Hey, Muhyiddin A.M Hayat sagte: > > Dear All, > > I Have below table > > id | site_name | point > ----+-----------+------- > 1 | Site A | 40 > 2 | Site B | 90 > 3 | Site D | 22 > 4 | Site X | 98 > > Would like to calc that Rank for each site, and look like > > > > id | site_name | point | rank > ----+-----------+-------+------ > 1 | Site A | 40 | 3 > 2 | Site B | 90 | 2 > 3 | Site D | 22 | 4 > 4 | Site X | 98 | 1 > > What can I do to get result like that create temporary sequence ranking; select *,nextval('ranking') as rank from yourTable order by site_name asc; Greetins, Martin Knipper -- Martin Knipper knipper@mk-os.de http://www.mk-os.de