Team,
I have a column called STATE and I need to pick the value based on the number of occurrence of that particular value.
Eg:
In this Eg I should be picking OH as it is occuring 3 times which is the max.
I use the below query to get this
SELECT ID,STATE FROM (
SELECT ID,STATE,
RANK() OVER (PARTITION BY ID ORDER BY CNT desc) AS TRANK FROM (
SELECT R.ID,R.STATE,
COUNT(*) CNT FROM
OPMASTER.DTL R
GROUP BY R.ID,R.STATE))
WHERE TRANK=1
But am facing problem when the value is unique
In this case since the values are unique , RANK is pulling both the values instead of one.In this case when the values are distinct I need to pull only latest ROWID. But am not able to fit in ROW ID in the above query
Any help is appreciated. Thanks