CODEWITHSUNDEEP
sqlsql-servergroup-byaggregate-functionsmin
Reeya Oberoi
Reeya Oberoi

Reputation: 861

Query to pull second smallest id

CUSTOMER(ID,CASE_ID,NAME,STATE)

          1,100,Alex,NY
          2,100,Alex,VA
          3,100,Alex,CT
          4,100,Tom,PA
          5,102,Peter,MO
          6,103,Dave,TN
          .
          .
          .

How to write a query to pull 2nd smallest (min) id (if present) for every group of case_id

Upvotes: 1

Views: 131

Answers (2)

lc.
lc.

Reputation: 116458

You can use a windowing function:

with cte as (
    select ID, CASE_ID, ROW_NUMBER() over (partition by CASE_ID order by ID) rn
    from CUSTOMER
)
select ID, CASE_ID
from cte
where rn = 2

Or you can use an exists clause to remove the first row (i.e. get the minimum value where there is a row with a lower value):

select MIN(ID) ID, CASE_ID
from CUSTOMER c
where exists (select 1 from CUSTOMER c2 where c2.ID < c.ID and c2.CASE_ID = c.CASE_ID)
group by CASE_ID

Or, written another way:

select MIN(ID) ID, CASE_ID
from CUSTOMER c
where c.ID > (select MIN(ID) from CUSTOMER c2 where c2.CASE_ID = c.CASE_ID)
group by CASE_ID

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Please try:

SELECT
    ID,
    CASE_ID
FROM
(
    SELECT 
        *,
        ROW_NUMBER() OVER(PARTITION BY CASE_ID ORDER BY ID) Rn
    FROM CUSTOMER
)x
WHERE Rn=2

Upvotes: 3

Related Questions