MSSQL query to select top 2 values

Question

I am not from a DBMS background and I couldn't find much help from reading some resources once.

I have a requirement like: There are 5 interfaces with some execution time. My goal is to find the top 2 interfaces which took the most time.

  RowNum   Interface    TIME 
  1        Intf1        0 
  2        Intf2        3 
  3        intf5        1 
  4        Intf2        4 
  5        intf3        3 
  6        Intf2        2 
  7        intf3        5 
  8        intf3        2 
  9        inft4        2 
 10        Intf2        0 
 11        intf5        1

The max time taken is 5 which corresponds to Intf3 (so print all intf3 with their times) and the second max time taken is 4 corresponding to Intf2(so print all intf2 with their times). So my expected output is:

RowNum  Interface   TIME
  7       intf3      5 
  5       intf3      3 
  8       intf3      2 
  4       Intf2      4 
  2       Intf2      3 
  6       Intf2      2 
  10      Intf2      0

I tried something like

select Row,Interface,Time 
from (select Row,Interface,Time from NAMES order by Time desc)

but it didn't seem to work as I was expecting.

score 1 · Accepted Answer · answered Mar 05 '21 at 20:45

There are different ways to do this ... Here is one

CREATE TABLE Table1
    ([RowNum] int, [Interface] varchar(5), [TIME] int)
;
INSERT INTO Table1
    ([RowNum], [Interface], [TIME])
VALUES
    (1, 'Intf1', 0),
    (2, 'Intf2', 3),
    (3, 'intf5', 1),
    (4, 'Intf2', 4),
    (5, 'intf3', 3),
    (6, 'Intf2', 2),
    (7, 'intf3', 5),
    (8, 'intf3', 2),
    (9, 'inft4', 2),
    (10, 'Intf2', 0),
    (11, 'intf5', 1)
SELECT t.*
FROM table1 as t
  INNER JOIN
  (
     SELECT Interface, Time , 
         ROW_NUMBER() OVER(ORDER BY a.Time dESC) as rn
     FROM Table1 as a
  ) AS P
  ON t.Interface = p.Interface
WHERE
  p.rn <=2
ORDER BY p.Time DESC,p.Interface,t.Time DESC

And output:

RowNum  Interface   TIME
7   intf3   5
5   intf3   3
8   intf3   2
4   Intf2   4
2   Intf2   3
6   Intf2   2
10  Intf2   0

dbfiddle

score 1 · Answer 2 · answered Mar 05 '21 at 20:47

This solution uses a CTE and INNER JOIN

CREATE TABLE tabl1
    ([RowNum] int, [Interface] varchar(5), [TIME] int)
;
INSERT INTO tabl1
    ([RowNum], [Interface], [TIME])
VALUES
    (1, 'Intf1', 0),
    (2, 'Intf2', 3),
    (3, 'intf5', 1),
    (4, 'Intf2', 4),
    (5, 'intf3', 3),
    (6, 'Intf2', 2),
    (7, 'intf3', 5),
    (8, 'intf3', 2),
    (9, 'inft4', 2),
    (10, 'Intf2', 0),
    (11, 'intf5', 1)
;
GO

WITH CTEmaxtime ([Interface],[maxtime]) AS
(SELECT TOP 2 [Interface],MAX([TIME]) FROM tabl1 
GROUP BY [Interface]
ORDER BY MAX([TIME]) DESC)
SELECT
  t1.[RowNum], t1.[Interface], t1.[TIME]
FROM tabl1 t1 INNER JOIN CTEmaxtime ct ON ct.[Interface] = t1.[Interface]
ORDER BY [maxtime] DESC ,[Interface], [TIME] DESC
GO

RowNum | Interface | TIME
-----: | :-------- | ---:
     7 | intf3     |    5
     5 | intf3     |    3
     8 | intf3     |    2
     4 | Intf2     |    4
     2 | Intf2     |    3
     6 | Intf2     |    2
    10 | Intf2     |    0

db<>fiddle here

MSSQL query to select top 2 values

2 Answers2