Sorting a Union query

Question

I have a pc, laptop and printer table, each of them having a price and model field.

I want to create a combined view of price and model number from these three tables, sorted by price. I have the following query:

SELECT p.model, p.price FROM ( 
  SELECT price, model FROM laptop  
    UNION  
  SELECT price, model  FROM pc 
    UNION 
  SELECT price, model  FROM printer    
 ) 
AS p order by p.price desc

Running this query does not sort on price, instead it returns the results sorted on model number. I checked similar question, but unable to make my query sort on price field.

model   price
1121    850.0000
1232    350.0000
1232    400.0000
1232    600.0000
1233    600.0000
1233    950.0000
1233    970.0000
1233    980.0000
1260    350.0000
1276    400.0000
1288    400.0000
1298    1050.0000
1298    700.0000
1298    950.0000
1321    970.0000
1401    150.0000
1408    270.0000
1433    270.0000
1434    290.0000
1750    1200.0000
1752    1150.0000

I am basically brushing up my sql skills and working on a problem given here http://sql-ex.ru/learn_exercises.php#answer_ref (question 24). — Anupam Saini, Aug 05 '17 at 11:06
@Pரதீப் . . . Doesn't matter. `price` is sorted neither numerically nor alphabetically. — Gordon Linoff, Aug 05 '17 at 11:49
@GordonLinoff That explains why it does not work. The following query too does not give sorted results::: SELECT model, price from laptop ORDER BY price DESC — Anupam Saini, Aug 05 '17 at 11:52

ScaisEdge · Answer 1 · 2017-08-05T11:25:39.003

0

you could order direcly this way

SELECT price, model FROM laptop  
UNION  
SELECT price, model  FROM pc 
UNION 
SELECT price, model  FROM printer 
order by price desc

could be you have string so try

SELECT price, model FROM laptop  
UNION  
SELECT price, model  FROM pc 
UNION 
SELECT price, model  FROM printer 
order by cast(price AS decimal(10,4)) desc

or for the ide you are using

select * from ( 
SELECT price, model FROM laptop  
UNION  
SELECT price, model  FROM pc 
UNION 
SELECT price, model  FROM printer  ) t
order by cast(price AS decimal(10,4)) desc

edited Aug 05 '17 at 11:25

answered Aug 05 '17 at 11:01

ScaisEdge

131,976
10
91
107

Thanks for the reply, but it did not work. I am basically brushing up my sql skills and working on a problem given here sql-ex.ru/learn_exercises.php#answer_ref (question 24). – Anupam Saini Aug 05 '17 at 11:07
Impossible .. this is basic sql .. are you sure you are using correct data set and data types ???? anyway answer updated with cast for decimal – ScaisEdge Aug 05 '17 at 11:13
This is what I get when i run your updated query http://imgur.com/a/2uAEB – Anupam Saini Aug 05 '17 at 11:19
Now the query works, but sorting on price is still broken :( http://imgur.com/a/4kzj3 – Anupam Saini Aug 05 '17 at 11:29
.. then you have some problem in your data .. or the data are number and must work thr firts .. or are string and once converted should work the sample 2 and 3 ... could be you have some problem with your ide .. try using console or another sql ide . .. – ScaisEdge Aug 05 '17 at 11:31
Thanks for your patience. I am basically practicing my sql skills on a website, think of topcoder like site but for sql. I am working on one of their practice problems (#24), which I have solved. Now I am trying to come up with a different approach and while working on that, I got this issue. So effectively, I do not have access to IDE or console and can not comment on the data types. I am assuming the price to be of floating type. – Anupam Saini Aug 05 '17 at 11:35
then you can use http://sqlfiddle.com .. because the sample provided by me and by Gordon Linoff work on mysql console or mysql TOAD and in sqlfiddle too .. – ScaisEdge Aug 05 '17 at 11:37
In mysql the data type money not exist so you should try with a proper data type .. decimal(10,4) or decimal(10,2) tipically – ScaisEdge Aug 05 '17 at 11:47

Gordon Linoff · Answer 2 · 2017-08-05T11:48:13.177

0

First, I would suggest that you use union all rather than union. It has better performance, because it does not incur overhead for removing duplicates.

Second, you can do this by adding order by:

SELECT price, model FROM laptop  
UNION ALL
SELECT price, model FROM pc 
UNION ALL
SELECT price, model FROM printer    
ORDER BY price DESC;

Some databases don't allow ORDER BY on union queries. In that case, a subquery is necessary:

SELECT model, price
FROM (SELECT price, model FROM laptop  
      UNION ALL
      SELECT price, model FROM pc 
      UNION ALL
      SELECT price, model FROM printer 
     ) pm   
ORDER BY price DESC;

This is essentially the query in your question. It should do what you intend.

edited Aug 05 '17 at 11:48

answered Aug 05 '17 at 11:03

Gordon Linoff

1,242,037
58
646
786

Thanks for the reply, but it did not work. I am basically brushing up my sql skills and working on a problem given here sql-ex.ru/learn_exercises.php#answer_ref (question 24). – Anupam Saini Aug 05 '17 at 11:09
`but it did not work` ... this should work; what happens when you run it? Oh dear, is `price` being stored as text? – Tim Biegeleisen Aug 05 '17 at 11:11
The result set it still sorted on the model number, as I has mentioned in my earlier comment, I am working on a sql problem #24 at http://sql-ex.ru/learn_exercises.php#answer_ref . I have been able to solve the problem, but was trying to sort the result set on price, that's when i got this problem. – Anupam Saini Aug 05 '17 at 11:13
@TimBiegeleisen Hopefully not :) It's site for practicing sql. – Anupam Saini Aug 05 '17 at 11:30

Sorting a Union query

2 Answers2