Question 247739 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql query result is unexpected

can anybody help?
there’s a table containing data about games…including:

name VARCHAR(255)
platform VARCHAR(255)
year_of_release YEAR
genre VARCHAR(255)
publisher VARCHAR(255)
na_sales DOUBLE(10,2)
other_sales DOUBLE(10,2)
global_sales DOUBLE(10,2)

when i run this query:

select Name, Year_of_Release, Other_Sales, Global_Sales from (
    select *
    from games
    where Year_of_Release >=2000
    order by Global_Sales desc, Name asc 
) a
union all
select Name, Year_of_Release, Other_Sales, Global_Sales from (
    select *
    from games
    where Year_of_Release < 2000
    order by Other_Sales desc, Name asc 
) b
;

i want the rows with year_of_release >=2000 appear first and after that rows with year_of_release<2000 appear.for the first group i want to sort by Global_sale and for second one by Other_sale…and if two rows was equal sale, sort by name in asc form.

Thanks for help.

i tried almost everything on the web,didn’t work.

Tags:

3

Answers


  1. 0

    If you want to keep the order of the subqueries you need to add LIMIT to the ORDER BY.

    select Name, Year_of_Release, Other_Sales, Global_Sales from (
    select *
    from games
    where Year_of_Release >=2000
    order by Global_Sales desc, Name asc 
    LIMIT 18446744073709551615
) a
union all
select Name, Year_of_Release, Other_Sales, Global_Sales from (
    select *
    from games
    where Year_of_Release < 2000
    order by Other_Sales desc, Name asc 
    LIMIT 18446744073709551615
) b
;
    Login or Signup to reply.
  2. 0

    its ordering must be done after the union query, looking like this:

    select Name, Year_of_Release, Other_Sales, Global_Sales from (
    select *
    from games
    where Year_of_Release >=2000
) a
union all
select Name, Year_of_Release, Other_Sales, Global_Sales from (
    select *
    from games
    where Year_of_Release < 2000
) b
ORDER BY Global_Sales DESC, Other_Sales DESC,Name asc;

    Your query could be done better, without having to use union, example:

    SELECT Name, Year_of_Release, Other_Sales, Global_Sales
FROM games
WHERE Year_of_Release >= 2000 OR (Year_of_Release < 2000 AND Global_Sales IS NOT NULL)
ORDER BY Global_Sales DESC, Other_Sales DESC, Name asc ;

    If you need to sort the result with year>=2000 first and then the lines with year<2000:

     SELECT Name, Year_of_Release, Other_Sales, Global_Sales
    FROM games
    ORDER BY 
        CASE
            WHEN Year_of_Release >= 2000 THEN 0
            ELSE 1
        END,
        Global_Sales DESC,
        Other_Sales DESC,
        Name asc ;
    Login or Signup to reply.
  3. 0

    Just wrap your query with another outer query 🙂

    This should be fairly easy by the following solution:

    SELECT * FROM (
    select Name, Year_of_Release, Other_Sales, Global_Sales from (
        select *
        from games
        where Year_of_Release >=2000
        order by Global_Sales desc, Name asc 
    ) a
    union all
    select Name, Year_of_Release, Other_Sales, Global_Sales from (
        select *
        from games
        where Year_of_Release < 2000
        order by Other_Sales desc, Name asc 
    ) b
) ORDER by Name asc;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search