Re: Sort and Limit Result

Thanks a lot

In my case order by count(*) causes an error.I use order by points or t1.points instead.

   "Jay Weiland" ...
  Hey Marinos,
  Assuming the 'name' field is unique, you could join the table to itself...

  NAME                                POINTS     DICOM 
  -------------------------------- --------- --------- 
  Jay                                     10        10 
  Marinos                                 11         8 
  Buzz                                     7         7 
  Woody                                    7         7 
  Jessie                                   5        12 
  Sully                                    2         0 
  Mike                                     1         3 

  select t1.name, t1.points, count(*) Rank   from sample_table t1, sample_table t2   where t1.points <= t2.points
  group by t1.name, t1.points
  having count(*) <= 3
  order by count(*)

  NAME                                POINTS      RANK 
  -------------------------------- --------- --------- 
  Marinos                                 11         1 
  Jay                                     10         2 

  ...there is not three results in this particular one since "Buzz" and "Woody" are tied at 4th.

  Jay!!!     

  Marinos wrote:

    i'm using sql plus 3.1 and i don't think that the order by in the subquery     is working
    --
    "TurkBear" ...
>
> Try this ( assuming you have a version of Oracle that supports it..(you
    failed to mention which you have))
>
> Select name,points from ( select name,points from table order by points
    desc) where rownum < 4 order by name,points desc;
>
> ( The subquery sorts the table by points high to low, then the main query
    limits the result set to the first ( in this case
> the top ) 3 records and sorts the result)....
>
> hth,
>
>

     "Marinos" ..
> >I have a table like this
> >name char
> >points number
> >dicom number
> >.
> >.
> >.
> >
> >How can i sort the table contents by points and show only the 3 lines
    with
> >the biggest values?
> >I have tried the rownum command with order by (select name, points from
> >table where rownum between 1 and 3 order by p desc;) but it's useless
> >it doesn't sort the whole table but only the first (inserted) 3 lines.
> >
> >any solution?
> >
> >Thanks in advance
Received on Wed Jan 16 2002 - 14:31:37 CST