Re: Problem re. ordering selection

In article <2rc972$hca_at_bigmig.auslig.gov.au> parnell_at_auslig.gov.au (Malcolm Parnell) writes:
>There exists a table X with columns and data thus :
>
>name num
>---- ---
>a 2
>b 10
>c 4
>d 7
>e 1
>f 9
>g 7
>
>I want to know what ranking "f" get if the table is ordered by num, name
>ie.
>
>e 1 --> rank 1
>a 2 --> rank 2
>c 4 --> rank 3
>d 7 --> rank 4
>g 7 --> rank 5
>f 9 --> rank 6
>b 10 --> rank 7
>
>How can I do this in a SQL statement?
>

try this  

select a.name, a.num, count(*) rank
from X a, X b
where ((a.num=b.num and a.name>=b.name) or (a.num>b.num))   and a.name='f'
group by a.name,a.num  

and you can generalize for all rows with :  

select a.name, a.num, count(*) rank
from X a, X b
where ((a.num=b.num and a.name>=b.name) or (a.num>b.num)) group by a.name,a.num
order by 3  

but the last one don't give a good performance for a big table, it will get at least O(n^2) [BEFORE the sort] if n is the number of fetchs data need internally for process the GROUP BY operation. Here i suppose an index for name,num on X. For this case i don't know how to get better performance if we are resticted to one SELECT only.  

Michel Tremblay
Universite de Montreal   Received on Thu May 19 1994 - 16:23:33 CEST