CODEWITHSUNDEEP
mysql
Guesser
Guesser

Reputation: 1857

MYSQL GROUP BY and ORDER BY with ABS

I need to select the "bundle" entries from the table below but only if a bundle exists other wise select track 1 from each release. Each release has a unique "cat_no" which is why I need to use GROUP BY.

SELECT * FROM cds  
WHERE genre='rock'  
GROUP BY cat_no  
ORDER BY ABS(track) DESC  
LIMIT 0,3

This what I have tried but I am sometimes getting track 1 when there is a bundle for that release.

Table

genre cat_no  track  
rock  001     1      
rock  001     2      
rock  001     bundle 
rock  002     1      
rock  002     2      
rock  002     bundle 
rock  003     1     
rock  003     2

Upvotes: 0

Views: 450

Answers (2)

Mosty Mostacho
Mosty Mostacho

Reputation: 43484

Try this:

select genre, cat_no, max(track) from t
where track in ('1', 'bundle')
group by genre, cat_no

We're actually taking advantage of the order ('bundle' appears after '1'). I don't understand the ABS on track... what's the intention of it?

Given your example data, the previous query would result in:

+-------+--------+--------+
| GENRE | CAT_NO | TRACK  |
+-------+--------+--------+
| rock  |      1 | bundle |
| rock  |      2 | bundle |
| rock  |      3 | 1      |
+-------+--------+--------+

Let me know if this helps.

Upvotes: 0

Devart
Devart

Reputation: 122002

Try to sort records before the grouping -

SELECT * FROM
  (SELECT * FROM cds WHERE genre = 'rock' ORDER BY IF(track = 'bundle', 0, 1)) t
GROUP BY
  cat_no;

Upvotes: 1

Related Questions