Using WITH TIES to retrieve multiple rows with same value

sqlitetop

I am relatively new to SQLite. I have a database called "Clothes Catalog" that contains info on each data point's item type (shirts, pants, etc), catalog ID, and price.

I want to retrieve the maximum price of an item of each type, the item type, and the catalog ID. If two items within a type have the same price, I want both catalog ID's to be included.

I tried this query but it does not run, any ideas why?

  SELECT TOP (1) WITH TIES itemtype, catalogID, price
  from clothes_catalog
  group by itemtype
  order by catalogID ASC;

Lets say my table looks like this:

 shirt, 20456, $16
 shirt, 23456, $18
 pant, 2222, $20
 pant, 4444, $20
 pant, 5656, $15

I want the following to show, and ALSO have catalogID in ascending order:

 pant, 2222, $20
 pant, 4444, $20
 shirt, 23456, $18

any suggestions?

Best Answer

This query returns the maximum price for each type:

SELECT itemtype,
       max(price)
FROM clothes_catalog
GROUP BY itemtype;

To get all items with that price, join these results back to the original table:

SELECT itemtype,
       catalogID,
       price
FROM clothes_catalog
JOIN (SELECT itemtype,
             max(price) AS price
      FROM clothes_catalog
      GROUP BY itemtype
     )
     USING (itemtype, price)
ORDER BY catalogID;
Related Question