Group and order by a column but donot include that column in results

Question

I've been trying to figure out how I can modify this query so that the result set does not include the numHits. I want the same results in the same order, just not have the numHits included.

SELECT 
    `newel_inventoryKeywordIdDictionaryId`.`inventoryId`
    ,COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) as numHits 
FROM 
    `newel_inventoryKeywordIdDictionaryId`
    , `newel_inventoryDictionary`
WHERE 
    `newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId` 
    AND ( 
        `newel_inventoryDictionary`.`word` = 'alabaster' OR `newel_inventoryDictionary`.`word` = 'chess'
    ) 
GROUP BY inventoryId 
ORDER BY numHits DESC;

sample results:

inventoryId, numHits
6928, 2
6929, 2
6924, 2
6925, 2
13772, 2
6926, 2
18203, 1
6931, 1
13863, 1
18402, 1

Desired Results:

inventoryId
6928
6929
6924
6925
13772
6926
18203
6931
13863
18402

Answer 1

Move the column from SELECT clause to ORDER BY clause:

SELECT 
    `newel_inventoryKeywordIdDictionaryId`.`inventoryId`
FROM 
    `newel_inventoryKeywordIdDictionaryId`
    , `newel_inventoryDictionary`
WHERE 
    `newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId` 
    AND ( 
        `newel_inventoryDictionary`.`word` = 'alabaster' OR `newel_inventoryDictionary`.`word` = 'chess'
    ) 
GROUP BY inventoryId 
ORDER BY COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) DESC;

Answer 2

SELECT 
`newel_inventoryKeywordIdDictionaryId`.`inventoryId`
FROM 
`newel_inventoryKeywordIdDictionaryId`
, `newel_inventoryDictionary`
WHERE 
`newel_inventoryKeywordIdDictionaryId`.`dicId` = `newel_inventoryDictionary`.`dicId` 
AND ( 
    `newel_inventoryDictionary`.`word` = 'alabaster' OR `newel_inventoryDictionary`.`word` = 'chess'
) 
GROUP BY inventoryId 
ORDER BY COUNT(`newel_inventoryKeywordIdDictionaryId`.`inventoryId`) DESC;

Answer 3

You just need to put the aggregation in the ORDER BY. However, you should also:

• Use explicit join syntax. Never use commas in the from clause.
• Use table aliases. They make queries easier to write and to read.
• Use in instead of a bunch of or statements.

Here is an improved version of the query:

SELECT kdi.inventoryId
FROM newel_inventoryKeywordIdDictionaryId kdi JOIN
     newel_inventoryDictionary id
     ON kdi.dicId = id.dictId
WHERE id.word IN ('alabaster', 'chess') 
GROUP BY kdi.inventoryId 
ORDER BY COUNT(*) DESC;