CODEWITHSUNDEEP
sql
TheVillageIdiot
TheVillageIdiot

Reputation: 40497

SQL: Count of rows before and after given row

The problem is that we are to get total number of rows before and after a given row (identified by, say, primary key).

I have tried following in T-SQL (MSSQL 2008). It is giving right result, but I don't know if this is best way to do it.

;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 accomplish in other SQL dialects and server (like Oracle, MySQL, sqlite, FireBird etc.)

Upvotes: 4

Views: 7578

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107706

Same idea as Philip's, but implemented properly and tested. This uses only stock standard ANSI SQL.

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 "A" alias finds the customer you want (55). The join to B produces one row for each customer, for which each row is tested for A.customerID.

The CASE clause produces

  • 1 ; when the condition < A.customerid[55] is met
  • null (implicit without an ELSE clause) when not met
  • Count skips null values, so the total comes out right

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

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

I think this will work in 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

Upvotes: 3

Philip Kelley
Philip Kelley

Reputation: 40309

An improved SQL version:

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

Upvotes: 0

Related Questions