SQL Server SELECT TOP from CTE based on subtraction of 2 column group by another

thtygnil 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(140)

I have data which is the results from a bunch of CTE and a query and this is where I am at.

Query (not including all previous CTE tables):

SELECT  
    CustomerCode,
    SUBSTRING(SerialNumber, CHARINDEX('-', SerialNumber) + 1, 6) SerialNumber,
    nbServer,
    ISNULL(nbImaging, 0) nbImaging
FROM 
    Magic
WHERE 
    CS_MagicNUmber <> I_MagicNumber
    OR CS_MagicNUmber IS NULL
    OR I_MagicNUmber IS NULL

Sample of resulting data:

What I want is to filter those results for each group of records sharing the same CustomerCode I.E. to get the TOP (nbServer - nbImaging) .

In the sample shown, for CustomerCode 20032455 is it simple as the difference is 3 (3-0) and I have 2 rows. But for the following CustomerCode , 20032625, I want the 5 first rows (12-7), so those with SerialNumber 805779, 805782, 805781, 805778, 805783 .

Of course there are a lot more but I think that shows what I want as a result. I have read multiple examples and also try PARTITION OVER but I must confess, nothing works.

sql-server

来源：https://stackoverflow.com/questions/75631876/select-top-from-cte-based-on-subtraction-of-2-column-group-by-another

2条答案

按热度按时间

xiozqbni1#

This is relatively straight forward with ROW_NUMBER as that just gives you ascending sequential integers from 1 that you can use in a WHERE clause.

I do also tuck the calculation of SerialNumber into a CROSS APPLY so I can reference it twice without repeating the expression.

WITH T AS
(
SELECT  CustomerCode
       ,ca.SerialNumber
       ,nbServer
       ,isnull(nbImaging,0) nbImaging
       ,ROW_NUMBER() over (partition by CustomerCode ORDER BY ca.SerialNumber) rn
FROM Magic
cross apply (select SUBSTRING(SerialNumber,CHARINDEX('-',SerialNumber)+1,6) SerialNumber) ca
WHERE CS_MagicNUmber <> I_MagicNumber
   OR CS_MagicNUmber IS NULL
   OR I_MagicNUmber IS NULL
)
SELECT *
FROM T
WHERE rn <= nbServer - nbImaging

赞(0）回复(0）举报 2023-03-07

mwkjh3gx2#

I don't understand why partition doesn't work for you. You want to count up a group of values, this is done by row_number:

select ROW_NUMBER()
over (partition by CustomerCode
order by something) AS sort

Now, you want to only get the first X records from each group. Since ROW_NUMBER cannot be in a WHERE, you need to wrap the whole thing in a subquery:

select *
from (
  select *
  , ROW_NUMBER() over(partition by CustomerCode order by something) AS sort
  from yourtable
 ) x
where sort <= nbServer - nbImaging

EDIT: or what @Martin wrote :)

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server SELECT TOP from CTE based on subtraction of 2 column group by another

2条答案

相关问题

热门标签

最新问答