Forum Stats

  • 3,757,128 Users
  • 2,251,200 Discussions
  • 7,869,734 Comments

Discussions

second highest salary

802304
802304 Member Posts: 130
edited Oct 14, 2011 3:12PM in SQL & PL/SQL
Hello,

I am using below query for second highest salary

select * from a_test e where 2 =(select count(distinct sal) from a_test where e.sal<=sal)

can anybody plz describe how where 2=.... works

what is the meaning of 2
MANOJ PRABHAKAR
«1

Answers

  • Arun Kumar Gupta
    Arun Kumar Gupta Member Posts: 996 Gold Badge
    2 is used for 2nd Highest salary...

    If you want 3rd highest salary it will be 3

    If you are looking for 2nd highest salary...the count will return 2 in the subquery for the employee who has two employees earning more than or equal to him (He himself)

    Similarly when you look for 3rd highest salary...the count will return 3 in the subquery for the employee who has three employees earning more than or equal to him (He himself)


    Regards
    Arun
  • 861956
    861956 Member Posts: 40
    Hi,

    The Query that you have mentioned above does is it'll check the where condition should equal to 2 or not.if it is 2 then it'll select the required results from TEST table.

    I think your query is wrong to get second highest salary. Correct me if I'm wrong.

    Try this.

    SELECT Salary FROM (SELECT DISTINCT salary FROM Table_Name ORDER BY Salary DESC) WHERE ROWNUM=2;

    What subquery does is returns all the distinct salaries. then you can get the second highest salary.

    Cheers,
    Naresh
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,027 Red Diamond
    edited Jun 2, 2011 6:20AM
    Hi,
    799301 wrote:
    Hello,

    I am using below query for second highest salary

    select * from a_test e where 2 =(select count(distinct sal) from a_test where e.sal<=sal)

    can anybody plz describe how where 2=.... works

    what is the meaning of 2
    Sorry, I don't think I understand.

    Do you understand what 3000 and = mean in this query?
    SELECT	*
    FROM	scott.emp
    WHERE	3000	= sal
    ;
    You must do queries like this all the time. There's not much difference between this query and the one you posted.
    There's a convention, when writing comparisons, especially equlity comparisons, to put the literal (when there is one) after the operator, so
    WHERE	sal	= 3000
    is more common than what I wrote earlier, but that's just a convention. The two WHERE clauses mean the same thing, and are equally efficient.

    You're using a more complicated expression than sal in your query. That doesn't change the meaning of literal or of =.
    Naresh wrote:
    ... I think your query is wrong to get second highest salary...
    Why do you think that? What's wrong about it? Post some sample data where OP's query produces the wrong results.
    I think OP's query is an accurate way to get all the information about rows with the 2nd highest sal. If we wanted just the sal (not who has it, or any other data from those rows), then something like your query would be better, of course. (Did you try either query?)

    Edited by: Frank Kulash on Jun 2, 2011 6:10 AM

    Edited by: Frank Kulash on Jun 2, 2011 6:18 AM
  • Vivek L
    Vivek L Member Posts: 638
    Naresh wrote:
    Try this.

    SELECT Salary FROM (SELECT DISTINCT salary FROM Table_Name ORDER BY Salary DESC) WHERE ROWNUM=2;
    you sure this works?? {noformat} ;) {noformat}
  • 847514
    847514 Member Posts: 254
    edited Jun 2, 2011 6:34AM
    >
    select * from a_test e where 2 =(select count(distinct sal) from a_test where e.sal<=sal)
    >

    This is a very old fashioned way of doing things (going back to oracle 7) when there are no analytical functions available. I hope you are aware how correlated subquery works. Query you have posted exactly works in the same fashion.

    Imagine the inner query returns a count for every single row from the outer query. Records will only be displayed when the inner query returns a value 2 which in other words is the second highest salary.

    Regards

    Raj

    P.S : BTW, this was one of the popular interview questions a decade ago.
  • 802304
    802304 Member Posts: 130
    Hi Naresh,

    your query is wrong! rownum=2 will not work.
  • raoul
    raoul Member Posts: 208
    One way is with a cursor to get the information sorted in descending order and then looping the result and getting the second value.
  • ora_1978
    ora_1978 Member Posts: 486 Bronze Badge
    edited Jun 2, 2011 10:11AM
    Try like this. But if you have duplicate salary this will not work.

    SELECT t1.Salary FROM (SELECT rownum, salary FROM Table_Name ORDER BY Salary DESC) t1 WHERE t1.rownum=2;


    or

    Taken care of duplicates.

    select t2.salary (SELECT rownum r, t1.Salary FROM (SELECT distinct salary FROM Table_Name ORDER BY Salary DESC) t1) t2 where t2.r = 2;


    thanks,
    Vinodh

    Edited by: Vinodh2 on Jun 2, 2011 5:10 AM

    Edited by: Vinodh2 on Jun 2, 2011 5:11 AM

    Edited by: Vinodh2 on Jun 2, 2011 7:08 AM
  • andyschwarz
    andyschwarz Member Posts: 312
    Since 8i there are analytical funcitions. Please do use them and not "rownum" which can result in very funny solutions...
    create table emp
    (id number,
    name varchar2(20),
    sal number);
    
    insert into emp values (1,'Fred', 10000);
    insert into emp values(2,'Joe', 12000);
    insert into emp values(3,'Tom', 6000);
    insert into emp values(4,'Jim', 6000);
    insert into emp values(5,'Tim', 7000);
    insert into emp values(6,'Jack', 5000);
    commit;
    
    select * from ( select e.*,
                    rank() over(order by sal)r,
                    row_number()  over(order by sal)rn
                    from emp e)
    order by rn;
    ID	NAME	SAL	R	RN
    6	Jack	5000	1	1
    3	Tom	6000	2	2
    4	Jim	6000	2	3
    5	Tim	7000	4	4
    1	Fred	10000	5	5
    2	Joe	12000	6	6
    Here you see that in this example there are 2 rows on rank 2 and none on 3.
    No "subselect" is needed and it runs VERY fast also on big tables.
  • pollywog
    pollywog Member Posts: 1,006
    edited Jun 2, 2011 9:09AM
    if you really want some overkill implement the secondmax function

    isn't it mythbusters that says if somethings worth doing its worth over doing.

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/aggr_functions.htm
    create type SecondMaxImpl as object
    (
      max NUMBER, -- highest value seen so far 
      secmax NUMBER, -- second highest value seen so far
      static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
        return number,
      member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, 
        value IN number) return number,
      member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
        returnValue OUT number, flags IN number) return number,
      member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, 
        ctx2 IN SecondMaxImpl) return number
    );
    
    create or replace type body SecondMaxImpl is 
    static function ODCIAggregateInitialize(sctx IN OUT SecondMaxImpl) 
    return number is 
    begin
      sctx := SecondMaxImpl(0, 0);
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateIterate(self IN OUT SecondMaxImpl, value IN number) return number is
    begin
      if value > self.max then
        self.secmax := self.max;
        self.max := value;
      elsif value > self.secmax then
        self.secmax := value;
      end if;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateTerminate(self IN SecondMaxImpl, 
        returnValue OUT number, flags IN number) return number is
    begin
      returnValue := self.secmax;
      return ODCIConst.Success;
    end;
    
    member function ODCIAggregateMerge(self IN OUT SecondMaxImpl, ctx2 IN SecondMaxImpl) return number is
    begin
      if ctx2.max > self.max then
        if ctx2.secmax > self.secmax then 
          self.secmax := ctx2.secmax;
        else
          self.secmax := self.max;
        end if;
        self.max := ctx2.max;
      elsif ctx2.max > self.secmax then
        self.secmax := ctx2.max;
      end if;
      return ODCIConst.Success;
    end;
    end;
    
    CREATE FUNCTION SecondMax (input NUMBER) RETURN NUMBER 
    PARALLEL_ENABLE AGGREGATE USING SecondMaxImpl;
    SELECT max(sal), SecondMax(sal), job
       FROM emp
       GROUP BY job
    
    MAX(SAL)	SECONDMAX(SAL)	JOB
    3000	3000	ANALYST
    1300	1100	CLERK
    2975	2850	MANAGER
    5000	0	PRESIDENT
    1600	1500	SALESMAN
    Edited by: pollywog on Jun 2, 2011 9:09 AM
This discussion has been closed.