Re: Sort and Limit Result

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 Tue Jan 15 2002 - 17:56:32 CST