获得最高记录和计数？
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 |
我想按名称和职业描述和计数顺序获取记录组。像这样的东西——
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
我尝试过这样的事情，但没有运气---
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;
我会做的
ROW_NUMBER() OVER(PARTITION BY EmailId ORDER BY qnumber DESC) AS rown
然后将其全部包装在具有WHERE rown = 1的外部查询中。外部查询还将计算S_NO，而不是内部查询
像这样：
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
但我不太确定你的"按计数排序"在哪里;对我来说，看起来你只是在获取最新的(根据qnumber(记录，对其他记录进行计数，并重新分配一些任意递增的数字作为s_no