Re: SQL Problem

for people who may referance the problem at a later date...... going back to the 1st problem of sorting the top 3 sals, would not work if there a 2 emps with the same sal in the top 3 earners.

the two solutions are :-

select rownum, ename, negsal*-1 possal
from (

   select ename, sal*-1 negsal
   from emp
   group by sal*-1, ename
   )
where rownum < 4
/

and for oracle 8i onwards

select rownum, ename, sal
from ( select * from emp
  order by sal desc)
where rownum < 4
/

"Arun Goel" <goel_ar1_at_hotmail.com> wrote in message news:<asbao5$obh3g$1_at_ID-127607.news.dfncis.de>...
> Hi all,
>
> I am facing problem in getting the desired result.
>
> Here is my problem..
> my table look like
> ----->temp(A,B,C);
> Data is as follows:
> A B C
> -- -- --
> A1 B1 C1
> A1 B1 C1
> A1 B1 C1
> A1 B1 C2
> A2 B2 C1
> A2 B2 C1
> A2 B2 C2
>
> For same A,B I need the rows with maximum of same C. & also want to know
> which C.
>
> I am able to find
> A1 B1 3
> A2 B2 3
> using this query.
> select A, B , MAX(Count_C ) as MAXC from
> (
> SELECT Testtable.A, Testtable.B, Count(Testtable.C) AS Count_C
> FROM Testtable
> GROUP BY Testtable.A, Testtable.B , Testtable.C
> ) GROUP BY A, B
>
> But I am mainly interested in what is the corresponding value of C for both
> rows.
> So what I need as result is:
> A1 B1 C1
> A2 B2 C1
>
>
> Thanks in advance
Received on Tue Dec 03 2002 - 13:25:01 CET