MySql/SQL Orderby and Condition -

i trying mysql/sql queries , learning tutorials complicated queries.

below query runs mentioned result set not understandable.

the query retrieves "2" highest paid employees each department.

select deptno, empno, sal emp e 2 > ( select count(e1.sal)       emp e1       e.deptno = e1.deptno , e.sal < e1.sal ) order 1,3 desc;  

but not able understand 2 > , order 1,3 in query.?

so understand query have run cause query separately follows:

select count(e1.sal)           emp e1           e.deptno = '1'; 

the above query returned me count of deptno value '1'.

so means each departments counted!!

i not sure happens in e.sal < e1.sal ?? running recursively?? calculate highest paid employees??

can 1 me in understanding query!

if can understand above logic can understand logic of query below:

4th top salary of employees:  select deptno, ename, sal emp 3 = (select count(b.sal) emp b a.sal < b.sal) order sal desc; 

sorry not providing fiddle..

this query correlated subquery.

to better understand subquery doing can move outer select , take @ returns

select deptno, empno, sal,       ( select count(e1.sal)           emp e1          e.deptno = e1.deptno , e.sal < e1.sal ) rank emp e 

sample output:

 | deptno | empno | sal | rank | ------------------------------- |      1 |   103 |  30 |    0 | |      1 |   102 |  20 |    1 | |      1 |   101 |  10 |    2 | |      2 |   201 | 100 |    0 | |      2 |   203 |  50 |    1 | |      2 |   202 |  40 |    2 | 

here sqlfiddle demo

what returns (for each record in outer select) number of rows same department salary less salary of row being retrieved in outer select ranking records in every department based on salary.

now where 2 > (subquery) same where (subquery) < 2 filters out rows rank higher 1 implementing top 2 rule.

order 1, 3 desc same order deptno, sal desc. 1 , 3 positions of deptno , sal columns in select clause.