0

Need to identify the most frequent occurrence of an item ID from an item description table, match it to its corresponding ID on an item sales table, and then display only the name from the description table corresponding to only the most frequently located item ID on the sales table. Does this query look remotely correct?

  SELECT ItemName FROM ItemDescriptionTable  
    WHERE ItemID =   
     (SELECT TOP 1 (ItemID)  
       FROM ItemSalesTable);
SQLdeficient
  • 1
  • 1

2 Answers2

0

Only very remotely, since neither mysql, nor sqlite supports top, plus top (or limit in mysql and sqlite) will give you the id of the record first inserted into the itemsales table this way. The idea to select the id in a subselect is good, though.

The occurances of ids can be retrieved by counting them in the itemsales table, order this resultset descendingly and limit the resultset to the 1st record.

SELECT ItemName FROM ItemTable  
WHERE ItemID =   
 (SELECT itemid from itemsales group by itemid order by count(itemid) desc limit 1);
Shadow
  • 33,525
  • 10
  • 51
  • 64
  • `count(itemid)` should be `count(*)`. Only use `count(colname)` when you need to exclude null values from the count. – Barmar Oct 08 '15 at 02:24
  • @Barmar Disagree, when do you expect the itemid to be null plus if itemid were null, I would not want that record to be counted anyway. – Shadow Oct 08 '15 at 02:29
  • You don't expect it to be null, that's the point. You shouldn't use `count(col)` unless you expect it to be null and need to skip those. http://stackoverflow.com/questions/2876909/count-and-countcolumn-name-whats-the-diff – Barmar Oct 08 '15 at 06:54
  • @Barmar Completele disagree with you. There is nothing in the SQL spec that says you should not do this, performance wise there will not be an impact, since this column must be not nullable. So, it is completely irrelevant which count option you use in this particular instance. – Shadow Oct 08 '15 at 10:36
  • OK, then I think it's just good style. It makes it clear that you're counting rows, not column values. ALso, are you sure that MySQL is smart enough to optimize it when the column is not nullable? – Barmar Oct 08 '15 at 10:39
  • Since I want to count column values (remember the question: most frequently used itemid), I absolutely belive that the suggested solution not only produces the expected results, but also has a good style and makes it clear what it does. I would rather discuss observations that actually add value to the topic. – Shadow Oct 08 '15 at 10:43
-1

SELECT ItemName , count(ItemName ) AS Item FROM ItemTable GROUP BY ItemName ORDER BY ITem DESC LIMIT 12;

Arun
  • 11
  • 5
  • Databases tagged do not support top, grouping itemids and selecting the 1st one would give you either the highest r the lowest itemid, not the most frequently occuring one – Shadow Oct 08 '15 at 02:32