GROUP BY one column, then by another column-CodePudding

SELECT lkey, max(votecount) FROM VOTES
WHERE ekey = (SELECT ekey FROM Elections where electionid='NR2019')
GROUP BY lkey
ORDER BY lkey ASC

Is there an easy way to get the pkey in this Statement?

Solution should look like this

CodePudding user response：

Use DISTINCT ON:

SELECT DISTINCT ON (v.ikey) v.*
FROM VOTES v
INNER JOIN Elections e ON e.ekey = v.ekey
WHERE e.electionid = 'NR2019'
ORDER BY v.ikey, v.votecount DESC;

In plain English, the above query says to return the single record for each ikey value having the highest vote count.