CODEWITHSUNDEEP
phpmysql
Hard worker
Hard worker

Reputation: 4066

How do I query the results of a 'group by' query, all within the same query

Imagine I have the following data in a table Example:

Price, Amount, Type
100, 3, A
80, 6, A
190, 16, A
50, 250, A
30, 2, B
55, 7, B
11, 3, B

In a single query I want to get all the elements of type A, ordered in ASCENDING order of price and all the elements of type B, ordering in DESCENDING order of price

I want to do it in a single query since the table updates very frequently and it would be disasterous if the data changed in between the two queries.

Is this possible?

Upvotes: 1

Views: 60

Answers (2)

Marc B
Marc B

Reputation: 360762

SELECT Price, Amount, Type
FROM yourtable
WHERE Type='A'
ORDER BY Price ASC

UNION ALL

SELECT Price, Amount, Type
FROM yourtable
WHERE Type='B'
ORDER BY Price DESC

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115610

SELECT Price, Amount, Type
FROM tableX
WHERE Type IN ('A','B')
ORDER BY Type, 
         CASE WHEN Type='A' THEN Price ELSE (- Price) END ;

Upvotes: 2

Related Questions