CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
kambythet
kambythet

Reputation: 716

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

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

Upvotes: 0

Views: 44

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

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;

Upvotes: 0

Arthur Shlain
Arthur Shlain

Reputation: 1109

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;

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272236

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;

Upvotes: 1

Related Questions