understanding sql query 'distinct' beginner

Question

Original table named Employee shows as follow:

------------------------
   Id      |     Salary
------------------------
    1       |     100
------------------------
    2       |      100
------------------------

query1 :select distinct * from Employee order by Salary desc limit 1,1;

result show:

------------------------
   Id      |     Salary
------------------------
    2      |     100
------------------------

query2 : select distinct Salary from Employee order by Salary desc limit 1,1;

result show:

empty set;

how to understand these two sql queries? I always think result of the query2 will be like this?

------------------------
      Salary
------------------------
      100
------------------------

any one can give me some suggestions?

mynawaz · Accepted Answer · 2015-09-05 06:27:33Z

1

The first query will return DISTINCT *, distinct combinations of id and salary which you have two i.e. two rows: 1, 100 & 2, 100. The use of LIMIT 1, 1 limits the result to 1 record starting from OFFSET 1 so you get the second record as a result of first query

The second query will return DISTINCT salary which you have only one i.e. 100. The use of LIMIT 1,1 again tries to limit result to 1 record starting from OFFSET 1 but as there is only one record to begin with, there is no record at the second offset, hence the empty set.

Check the LIMIT section on this https://dev.mysql.com/doc/refman/5.0/en/select.html

answered Sep 5, 2015 at 6:27

mynawaz

1,5941 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

understanding sql query 'distinct' beginner

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related