SQL: rows before and after a given row

Question

The problem is that we need to get the total number of lines before and after a given line (identified by, say, the primary key).

I tried following in T-SQL (MSSQL 2008). This gives the correct result, but I do not know if this is the best for this.

;WITH cte_before AS ( SELECT ROW_NUMBER() OVER ( Order By CustomerId ) [Row Number], customerid, firstName FROM SalesLT.Customer ), cte_nums AS ( SELECT ROW_NUMBER() OVER ( Order By CustomerId ) [Row Number1] FROM SalesLT.Customer ) SELECT [Row Number]-1 [before], MAX([Row Number1]) - [Row Number] , CustomerID, FirstName FROM cte_nums, cte_before GROUP BY [Row Number], CustomerID, FirstName HAVING CustomerID = 55 

How can we improve it in T-SQL and how can we achieve it in other dialects of SQL and the server (for example, Oracle, MySQL, sqlite, FireBird, etc.).

Answer 1

I think this will work on any dialect:

 select (select count(*) from SalesLT.Customer where customerid < 55) as rows_before, (select count(*) from SalesLT.Customer where customerid > 55) as rows_after, CustomerID, FirstName from SalesLT.Customer where CustomerID = 55 

Answer 2

The same idea as Philip, but implemented correctly and tested. This uses only the standard ANSI SQL standard.

 SELECT A.customerid, A.firstName, count(case when B.customerid < A.customerid then 1 end) count_before, count(case when B.customerid > A.customerid then 1 end) count_after FROM SalesLT.Customer A cross join SalesLT.Customer B where A.customerID=55 GROUP BY A.customerid, A.firstName 

The pseudonym “A” finds the desired customer (55). A connection to B creates one row for each client, for which each row is tested for A.customerID.

CASE clause creates

• 1; when the condition <A.customerid [55] occurs
• null (implicit without ELSE clause) when not encountered
• Count skips zero values, so the total number is obtained on the right

GROUP BY is required to use COUNT (), an aggregate function

Answer 3

Improved version of SQL:

 DECLARE @CustomerId int SET @CustomerId = 55 SELECT @CustomerId ,ThisOne.FirstName ,sum(case when Agg.CustomerId < @CustomerId then 1 else 0 end) [Before] ,sum(case when Agg.CustomerId > @CustomerId then 1 else 0 end) [After] from SalesLT.Customer Agg inner join SalesLT.Customer ThisOne on ThisOne.CustomerId = @CustomerId