SQL Server How to get first top values of a column, then perform where conditions to reduce rows

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

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

SELECT TOP 25 customer
FROM tt
WHERE DocNum LIKE '%something%' AND U_NAME NOT LIKE '%something%'
GROUP BY customer
ORDER BY MIN(DocNum) DESC

Let's say I have the query above but obviously it returns the top 25 row of

SELECT customer 
FROM tt 
WHERE DocNum LIKE '%something%' 
  AND U_NAME NOT LIKE '%something%' 
GROUP BY customer 
ORDER BY MIN(DocNum) DESC

How can I get the

WHERE DocNum LIKE '%something%' 
  AND U_NAME NOT LIKE '%something%' 
GROUP BY customer 
ORDER BY MIN(DocNum) DESC

of the top 25 rows of customer?

sql-server

来源：https://stackoverflow.com/questions/75601010/how-to-get-first-top-values-of-a-column-then-perform-where-conditions-to-reduce

2条答案

按热度按时间

siv3szwd1#

You want the top 25 customers (which are the 25 with the least docnums) first. For these customers you want to select all rows that match certain docnum and u_name patterns:

WITH top25 AS
(
  SELECT TOP 25 customer
  FROM tt
  GROUP BY customer
  ORDER BY MIN(docnum) DESC
)
SELECT *
FROM tt
WHERE docnum LIKE '%something%' AND u_name NOT LIKE '%something%'
AND customer IN (SELECT customer FROM top25)
ORDER BY customer, docnum;

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

0aydgbwb2#

The WHERE clause is evaluating before the TOP and ORDER BY . If you want to first get the TOP 25 records order by MIN(DocNum) DESC then you need to perform this operation first. This can be achieved using sub-query like the following:

SELECT customer
FROM tt 
INNER JOIN 
(
    SELECT TOP 25 customer
    FROM tt
    GROUP BY customer
    ORDER BY MIN(DocNum) DESC
) ds
    ON tt.customer = ds.customer
WHERE tt.DocNum LIKE '%something%' 
    AND tt.U_NAME NOT LIKE '%something%'

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

我来回答

SQL Server How to get first top values of a column, then perform where conditions to reduce rows

2条答案

相关问题

热门标签

最新问答