-3

I have data stored as:

TestId  | Priority |
--------+-----+-----
test103 | 2        | 
test104 | 2        | 
test105 | 1        | 
test106 | 0        | 
test107 | 1        | 
test108 | 2        | 
test109 | 0        | 
test110 | 1        | 
test111 | 1        |

I want to be able to select top 2 based on priority like below.

TestId  | Priority |
--------+-----+----- 
test106 | 0        | 
test109 | 0        |

We have over a million records and I would like a query that is more efficient in terms on performance. Please any help would be much appreciated

7
  • What happens when the testid goes over 999? Version of SQL Server?
    – Vérace
    Dec 17, 2021 at 18:09
  • please ignore the testid column as we use an incremental integer, so we could have 1, 2, 3,4 and so on. The version is 2017
    – user244947
    Dec 17, 2021 at 18:13
  • 2
    Unless I've missed something obvious, what's wrong with select * from tab order by priority, testid;?
    – Vérace
    Dec 17, 2021 at 18:31
  • Performance is a problem, takes about 17sec. Was asking for a better way to approach the query that is performant
    – user244947
    Dec 17, 2021 at 18:45
  • 3
    Is the highest Priority always 0? Is there always at least two records in the table with Priority = 0? Which "top 2" records would you want when there are 3 records with Priority = 0?
    – J.D.
    Dec 17, 2021 at 20:49

1 Answer 1

1

To get TOP 2 from the table described above:

SELECT TOP(2) TestID, Priority FROM test_table ORDER BY Priority ASC

BUT You need to have an index on the table with definition as follows:

CREATE INDEX IX_test_table ON test_table (Priority) INCLUDE (TestId);

With this index, the query reads 2 rows and is finished as the execution plan below shows. This applies regardless of table size. TOP operator effectively works as "execution terminator" here, because as soon as he gets 2 rows from the index scan operator, the query is finished.

Execution plan resulting from tests of OP's problem

As J.D. said, if there are more than 2 results with lowest prioriry (given you search for lowest priority), these aren't returned, but I bet you are aware of that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.