2

I don't really know how to ask this, but here is the database setup as example:

| id | collectionId | cardNum | price |
| 1  |      1       |    1    |  0.10 |
| 2  |      1       |    1    |  5.00 |
| 3  |      1       |    2    |  0.30 |
| 4  |      1       |    3    |  0.45 |
| 5  |      1       |    4    |  0.65 |
| 6  |      1       |    5    |  1.00 |
| 7  |      2       |    1    |  0.10 |
| 8  |      2       |    1    |  5.10 |
| 9  |      2       |    2    |  0.30 |

My goal is to have a result like this:

Id: 8, price: 5.10
id: 2, price: 5.00
id: 6, price: 1.00
id: 5, price: 0.65
id: 4, price 0.45
id: 9, price: 0.32
id: 3, price: 0.30

It omits duplicate cardNum from the same collectionId while ordering by price

I have tried multiple versions of GROUP BY and JOINS to no avail, the closest I have come is:

SELECT id FROM table_name GROUP BY cardNum,collectionId ORDER BY price DESC

But this only grabs the id of the first occurrence without attention to price.

Is this even possible, or what would be a method to achieve my goal?

These are all the things I have tried:

SELECT id FROM table_name GROUP BY cardNum,collectionId ORDER BY price DESC

SELECT 
  t1.id
FROM 
  table_name AS t1
LEFT JOIN 
  table_name AS t2
ON 
  t1.cardNum < t2.cardNum
AND 
  t1.collectionId = t2.collectionId
GROUP BY 
  t1.cardNum,t1.collectionId
ORDER BY 
  t1.price DESC

SELECT 
  t1.id
FROM 
  table_name AS t1
LEFT JOIN 
  table_name AS t2
ON 
  t1.collectionId = t2.collectionId
AND (
  t1.cardNum > t2.cardNum
  OR (
    t1.collectionId = t1.collectionId AND t1.cardNum = t2.cardNum AND t1.id > t2.id
  )
)
GROUP BY 
  t1.collectionId,t1.cardNum
ORDER BY 
  t1.price DESC

SELECT 
    id,
    max(price)
 FROM
    table_name 
 GROUP BY 
    cardNum,collectionId
ORDER BY price DESC

SELECT 
  id,
  MAX(price)
FROM 
  table_name 
GROUP BY 
  cardNum,collectionId 
ORDER BY 
  MAX(price) DESC

This gives me the max price as well as id to identify the unique collectionId and cardNum all in descending order

Dharman
  • 30,962
  • 25
  • 85
  • 135
user2511559
  • 23
  • 3
  • Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/q/121387/283366) – Phil Oct 26 '22 at 01:14
  • @Cositanto This query is wrong, it contains incomplete grouping and returns inpredictable values for the columns which are not used in GROUP BY expression. – Akina Oct 26 '22 at 04:58
  • *It omits duplicate cardNum from the same collectionId while ordering by price* What is the criteria of this omitting? Store only the row with greatest `id`? greatest `price`? something else? you must define this unambiguously. – Akina Oct 26 '22 at 05:00

0 Answers0