1

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?

  • Just to add an explanation "why": because TOP is not standard SQL (it's specific to MS SQL Server), likewise for WITH TIES. SQLite's version of TOP is LIMIT (which also isn't standard, although it's a more common variant than TOP). The "standard" is FETCH FIRST which (AFAIK) you'll only find in Postgres and DB2! Welcome to the many not-quite-compatible varieties of SQL... – Dave Jones Apr 27 '17 at 13:11
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.