select top record and count from each group oracle?

Question

Get top record and count?

PKid    |    QId    |    QNumber    |    EmailId    |    FirstName    |    LastName    |
1    |    102    |    A1022    |    jsmith@test.com    |    John    |    Smith    |
2    |    103    |    A1021    |    jsmith@test.com    |    John    |    smith    |
3    |    104    |    A1031    |    jblack@test.com    |    Jack    |    Black    |
4    |    105    |    A1032    |    jblack@test.com    |    Jack    |    black    |
5    |    106    |    A1023    |    jsmith@test.com    |    John    |

I want to fetch records group by name and order by occupation desc and count. Something like this-

  S.no    |    QId    |    QNumber    |    EmailId    |    FirstName    |    LastName    |    Count
1    |    106    |    A1023    |    jsmith@test.com    |    John    |    |    3 
2    |    105    |    A1032    |    jblack@test.com    |    Jack    |    black    |    2

I tried something like this but no luck---

    SELECT
    ROW_NUMBER() OVER(
        ORDER BY
            COUNT(1) DESC
    ) AS S_NO,QId,
    MAX(QNUMBER) AS QNUMBER,
    EmailId,FirstName,LastName
    COUNT(1)
FROM
    TblEmp   
GROUP BY
    EmailId; 

Answer 1

I would have done

ROW_NUMBER() OVER(PARTITION BY EmailId ORDER BY qnumber DESC) AS rown

and then wrapped it all in an outer query that has WHERE rown = 1. The outer query would also calculate the S_NO, not the inner query

Something like this:

SELECT 
  ROW_NUMBER() OVER(ORDER BY qnumber) AS S_NO,
  ee.*
FROM
(
    SELECT
      ROW_NUMBER() OVER(PARTITION BY EmailId ORDER BY qnumber DESC) AS rown,
      COUNT(*) OVER(PARTITION BY EmailId) AS count,
      e.*
    FROM
        TblEmp e   

) ee
WHERE ee.rown = 1

But i'm not really sure where your "order by count" fits in; to me it just looks like you're taking the latest (according to qnumber) record, taking a count of the other records, and reassigning some arbitrary incrementing number as s_no