CODEWITHSUNDEEP
sqlt-sql
BigIWT
BigIWT

Reputation: 293

Count records in table and show line number of specific value

I'm looking to find the position of a value within a result set AND the total number of results in that set.

In the example below, ID-101 is part of a set of 3 records, and is located at position #2 within that set.

tblData

ID     Type  Order   
100    P     1       
101    P     2       
102    P     3

Result

cntRec    intPos
3         2

Example #2
In the example below, ID-101 is part of a set of 3 records, the ordering is changed (by the user) and is located at position #1 within that set.

tblData

ID     Type  Order   
100    P     3       
101    P     1       
102    P     2

Result

cntRec    intPos
3         1

Upvotes: 1

Views: 312

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

Please find the query with the window function:

DECLARE @intPos AS INT = 0;

;WITH RowCountCte AS (
   SELECT ID, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS intPos
   FROM tblData
)

SELECT @intPos = intPos FROM RowCountCte WHERE ID = 101;

SELECT COUNT(ID) AS cntRec, @intPos AS intPos
FROM tblData;

Demo on db<>fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269723

Is this what you want?

select count(*) as cntrec,
       sum(case when id <= 101 then 1 else 0 end) as intpos
from t;

Upvotes: 1

Related Questions