MySQL: Basic Order by & Limit sub-clause

1. Using basic 'order by'

# Order stu by stu_score as the order of descendant
select * from stu order by stu_score desc;

 

2. Using 'order by' to order multiple columns

# Order table 'stu' by stu_score
# If two row's stu_score are the same, then use stu_name as secondary order restriction
select * from stu order by stu_score desc, stu_name desc;

 

3. Using 'order by' together with 'limit'

# Select the highest two stu_score
select * from stu order by stu_score asc limit 2;

 

4. Limit [offset], [N]

    offset: default 0;

    N:        The number of item to be selected.

    <Limit is useful in paging>

 

    Comment:

        How can we fetch the stu_course for each student whose highest score belongs to this course?

 

    Answer:

        1. First, order the table stu as the stu_name as primary order regulator.

        2. Second, set the stu_score as the secondary order regulator.

        3. Last, regard the result set as a virtual table and the execute the query based on this table.

select * from (select * from stu order by stu_name asc, stu_score desc) as temp group by stu_name;

 

    Summary:

        1) where expression

            To see if the expression is true on each row then fatch this row.

            =, !=, <>, >, <, <=, >=

            in, between and

            or, and, not

        2) group by

            Is used for grouping, usually used together with statistic function.

            sum, count, max, min, avg

        3) having expression

           If the expression is true when used in where clause, then it can be applied in having expression.

           But if the expression is based on virtual table(or result set), then it can only applied in having expression. While expression can only applied for real table that stored on disk.

        4) order by

           Can be applied for order real table or result set as the order of [asc] or [desc].

           Multiple compare rules are enabled.

        5) limit

          Can be applied for result set for paging or limit the shown item.