Re: Problem re. ordering selection

In article <Cq1zzC.L7H_at_cc.umontreal.ca> tremblam_at_JSP.UMontreal.CA (Tremblay Michel) writes:
>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

 [...]
>>I want to know what ranking "f" get if the table is ordered by num, name
>>
>>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
>
 

No need for join/group to do the single-value summary. This works:  

  select count(*) rank from X
   where num < (select num from X where name = 'f')

      or ( num = (select num from X where name = 'f')
       and name < 'f' )
 

To get all the values at once you need the join:  

  select a.name, count(*) rank from X a, X b

   where   b.num < (select num from X where name = a.name)
      or ( b.num = (select num from X where name = a.name)
       and b.name < a.name )
  group by a.name
 

and indexes will help if the table's not small. one (unique) on name, one on num. Maybe turn the name index into name,num so the subquery can get num without having to read the row for it.  

Jim  

p.s. just saw a followup suggesting an index with a dummy where clause to force index use. I suspect this will fail on large tables with the cost-based optimizer: it could very easily (and imho should) notice that the where will select all rows and just do a straight scan. You'd have to select and order by num,name. Haven't tried that, not a db kernel guy. Received on Wed May 25 1994 - 09:59:58 CEST