Suppose I have created the following query (I use SQL Server), which returns the following output:

SELECT *
FROM DB
ORDER BY CLIENT_ID

In such case how can I update my above query to select only the 2 last CLIENT ID, and I should be able to use whatever other number like last 20, last 60, last 100, etc

In my example the expected output would be

meaning that we see only the rows related to the 2 last clients which are client B99 and C93 (meaning that first client A19 is filtered out since it does not belong to the last 2)

CodePudding user response：

This will give you the expected output. But as others already mentioned it's unclear what last mean. I'm just guessing from your expected result. Also please don't post photo of tables next time.

SELECT A.CLIENT_ID, A.PRICE_BILL
FROM DB A
WHERE A.CLIENT_ID IN (
    SELECT DISTINCT TOP(2) A.CLIENT_ID
    FROM DB A
    ORDER BY A.CLIENT_ID DESC
)
ORDER BY A.CLIENT_ID ASC, A.PRICE_BILL ASC

See Demo

CodePudding user response：

You can accomplish what you require using dense_rank() and filtering out the last 2 rankings.

The reason you use dense_rank is because it assigns the same ranking to ties thereby ranking all of the same CLIENT_ID the same. Also note the reverse ordering of the dense_rank to make it easy to filter out the last 2 values because they are ranked 1 & 2.

declare @MyTable table (CLIENT_ID varchar(3), PRICE_BILL int);

insert into @MyTable (CLIENT_ID, PRICE_BILL)
values
('A19',91), ('A19',29), ('A19',92)
, ('B99',85), ('B99',202)
, ('C93',399), ('C93',929), ('C93',929);

with cte as (
    select *
        , dense_rank() over (order by CLIENT_ID desc) dr
    from @MyTable
)
select *
from cte
where dr < 3
order by CLIENT_ID;

Returns:

fiddle

Note the provision of sample data as DDL DML makes it much easier for people to assist.