SQL query , retrieve except last 10 products with lowest product number-CodePudding

I got stuck on this, I would like to retrieve the number of all products, except for the last 10 products with the lowest product number.

I have tried this but I know how to check if its right code or wrong could someone confirm?

SELECT P1.PRODNR
FROM PRODUCT P1
WHERE 10 < (SELECT COUNT(*)
            FROM PRODUCT P2
            WHERE P1.PRODNR < P2.PRODNR)

CodePudding user response：

You can do this with the help of a CTE and row_number

with n as (
    select prodnr, Row_Number() over(order by prodnr) rn
    from products
)
select prodnr
from n
where rn>10

CodePudding user response：

Subquery to get the list of low numbers to exclude

SELECT P1.PRODNR
FROM PRODUCT P1
WHERE PRODNR not in
(SELECT top 10 PRODNR 
FROM PRODUCT P2 
order by PRODNR)  -- assuming product number is a number

CodePudding user response：

Here is another way of acheiving your task:

SELECT P1.PRODNR
FROM PRODUCT P1
WHERE P1.PRODNR < (select min(prodnr) from 
                      (SELECT top 10 PRODNR FROM PRODUCT P2) P3
                  )