sql - First two records of each group based on rank - TagMerge
2First two records of each group based on rankFirst two records of each group based on rank

First two records of each group based on rank

Asked 5 months ago
0
2 answers

SELECT b.*
FROM
(SELECT a.[sap_id]
,a.[wr_nbr]
,a.[start_date]
,a.[end_date]
,a.[vs_ind]
,a.[rn]
,COUNT(*) OVER (PARTITION BY a.sap_id, a.wr_nbr) as count_rows
FROM
(SELECT [sap_id]
  ,[ts_nbr]
  ,[wr_nbr]
  ,[check_line]
  ,[check_nbr]
  ,[start_date]
  ,[end_date]
  ,[vs_ind]
  ,[rn]
  ,[rank_ind]
   FROM [dbo].[first_two]) a) b
WHERE b.count_rows > 1
AND b.rn <= 2

Final table looks like:

enter image description here

Source: link

0

Solution:
WITH added_row_number AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY year ORDER BY result DESC) AS row_number
  FROM exam_results
)
SELECT
  *
FROM added_row_number
WHERE row_number = 1;
Take a look at the result of the inner query:
SELECT
  *,
  ROW_NUMBER() OVER(PARTITION BY year ORDER BY result DESC) AS row_number
FROM exam_results;
Note that you can easily modify the solution to get, for example, the second row of each group.
WITH added_row_number AS (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY year ORDER BY result DESC) AS row_number
  FROM exam_results
)
SELECT
  *
FROM added_row_number
WHERE row_number = 2;
On the other hand, if you want to get the row(s) with the second highest value of result within each group, you should use the DENSE_RANK() function. While the ROW_NUMBER() function creates consecutive numbers for each row in a group, resulting in different values assigned to the rows with the same result, the DENSE_RANK() function gives the same number to the rows with the same result.
WITH added_dense_rank AS (
  SELECT
    *,
    DENSE_RANK() OVER(PARTITION BY year ORDER BY result DESC) AS rank
  FROM exam_results
)
SELECT
  *
FROM added_dense_rank
WHERE rank = 2;

Source: link

Recent Questions on sql

    Programming Languages