• Sorting in MySQL : ORDER BY, DESC and ASC?

    Answered

    Sorting in MySQL : ORDER BY, DESC and ASC?

    Bronze Member Asked on August 11, 2016 in MYSQL.
    Add Comment
  • 3 Answer(s)
    Best answer

    Sorting Results

    We looked at how to get data from our tables using the SELECT command. Results were returned in the same order the records were added into the database. This is the default sort order. In this section, we will be looking at how we can sort our query results.Sorting is simply re-arranging our query results in a specified way. Sorting can be performed on a single column or on more than one column. It can be done on number, strings as well as date data types.

     

    Order by clause

    The order by clause is used to sort the query result sets in either ascending or descending order. It is used in conjunction with the SELECT query. It has the following basic syntax.

     

    SELECT statement... [WHERE condition | GROUP BY `field_name(s)` HAVING condition] ORDER BY `field_name(s)` [ASC | DESC];

    HERE

    • “SELECT statement…” is the regular select query
    • ” | “ represents alternatives
    • “[WHERE condition | GROUP BY `field_name(s)` HAVING condition” is the optional condition used to filter the query result sets.
    • “ORDER BY” performs the query result set sorting
    • “[ASC | DESC]” is the keyword used to sort result sets in either ascending or descending order. Note ASC is used as the default.

    What are DESC and ASC Keywords?

    altASC is the short form for ascending altDESC is the short form for descending
    It is used to sort the query results in a top to bottom style. It is used to sort the query results in a bottom to top style
    When working on date data types, the earliest date is shown on top of the list. . When working on date types, the latest date is shown on top of the list.
    When working with numeric data types, the lowest values are shown on top of the list. When working with numeric data types, the highest values are shown at top of the query result set.
    When working with string data types, the query result set is sorted from those starting with the When working with string data types, the query result set is sorted from those starting with the letter Z going down to the letter A letter A going up to the letter Z. When working with string data types, the query result set is sorted from those starting with the letter Z going down to the letter A.

     

    Both the DESC and ASC keywords are used together in conjunction with the SELECT statement and the ORDER BY clause.

    DESC and ASC syntax

    The DESC sort keyword has the following basic syntax.

    SELECT {fieldName(s) | *} FROM tableName(s) [WHERE condition] ORDER BY fieldname(s) ASC /DESC [LIMIT N]

    HERE

    • SELECT {fieldName(s) | *} FROM tableName(s) is the statement containing the fields and table(s) from which to get the result set from.
    • [WHERE condition] is optional but can be used to filter the data according to the given condition.
    • ORDER BY fieldname(s) is mandatory and is the field on which the sorting is to be performed. The DESC keyword specifies that the sorting is to be in descending order.
    • [LIMIT] is optional but can be used to limit the number of results returned from the query result set.

    Why we may use DESC and ASC?

    Suppose we want to print a payments history for a video library member to help answer queries from the front desk, wouldn’t it be more logical to have the payments printed in a descending chronological order starting with the recent payment to the earlier payment? The DESC key word comes in handy in such situations. We can write a query that sorts the list in descending order using the payment date. Suppose the marketing department wants to get a list of movies by category that members can use to decide which movies are available in the library when renting movies, wouldn’t it be more logical to look sort the movie category names and title in ascending so that members can quickly lookup the information from the list? The ASC keyword comes in handy in such situations; we can get the movies list sorted by category name and movie title in an ascending order.

    Summary

    • Sorting query results is re-arranging the rows returned from a query result set either in ascending or descending order.
    • The DESC keyword is used to sort the query result set in a descending order.
    • The ASC keyword is used to sort the query result set in an ascending order.
    • Both DESC and ASC work in conjunction with the ORDER BY keyword. They can also be used in combination with other keywords such as WHERE clause and LIMIT
    • The default for ORDER BY when nothing has been explicitly specified is ASC
    Bronze Member Answered on August 11, 2016.
    Add Comment
    SELECT * FROM results ORDER BY SUBSTRING( qid FROM 1 FOR 1 ) ASC , marks DESC
    
    Bronze Member Answered on August 11, 2016.
    Add Comment

    , I THINK I understand what you want now, and let me clarify to confirm before the query. You want 1 record for each user. For each user, you want their BEST POINTS score record. Of the best points per user, you want the one with the best average time. Once you have all users “best” values, you want the final results sorted with best points first… Almost like ranking of a competition.

    So now the query. If the above statement is accurate, you need to start with getting the best point/average time per person and assigning a “Rank” to that entry. This is easily done using MySQL @ variables. Then, just include a HAVING clause to only keep those records ranked 1 for each person. Finally apply the order by of best points and shortest average time.

    
    select
    
    U.UserName,
    
    PreSortedPerUser.Point,
    
    PreSortedPerUser.Avg_Time,
    
    @UserRank := if( @lastUserID = PreSortedPerUser.User_ID, @UserRank +1, 1 ) FinalRank,
    
    @lastUserID := PreSortedPerUser.User_ID
    
    from
    
    ( select
    
    S.user_id,
    
    S.point,
    
    S.avg_time
    
    from
    
    Scores S
    
    order by
    
    S.user_id,
    
    S.point DESC,
    
    S.Avg_Time ) PreSortedPerUser
    
    JOIN Users U
    
    on PreSortedPerUser.user_ID = U.ID,
    
    ( select @lastUserID := 0,
    
    @UserRank := 0 ) sqlvars
    
    having
    
    FinalRank = 1
    
    order by
    
    Point Desc,
    
    Avg_Time
    
    

    Results as handled by SQLFiddle

    Note, due to the inline @variables needed to get the answer, there are the two extra columns at the end of each row. These are just “left-over” and can be ignored in any actual output presentation you are trying to do… OR, you can wrap the entire thing above one more level to just get the few columns you want like

    
    select
    
    PQ.UserName,
    
    PQ.Point,
    
    PQ.Avg_Time
    
    from
    
    ( entire query above pasted here ) as PQ
    
    
    Bronze Member Answered on August 11, 2016.
    Add Comment
  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.