CODEWITHSUNDEEP
sqlsql-servert-sql
Mille Bii
Mille Bii

Reputation: 195

Select first occurence from SQL Result

This query

select  AM.Id, convert(int,sub.Version) as SubVersion , RP.Id as RPId 
from sub 
left join ZRP as RP on RP.sid=sub.id
left join ZAM as AM on AM.Id=RP.AmId
where SUB.Name='xxxx' and AmId<>''
order by  AM.Id, SubVersion

Returns the following result

Id SubVersion RPId
8a288b2976f5afd50176f5eeed941237 4 8a288b2976f5afd50176f5eeedba123c
8a288b2976f5afd50176f5eeed941237 5 8a28a00576f5afc70176f78489f30c75
8a288b2976f5afd50176f5eeed941237 6 8a28f1aa76f5c78c0176f7848f6e583e
8a28a00576f5afc70176f78489a20c68 5 8a28a00576f5afc70176f78489c20c6d
8a28a00576f5afc70176f78489a20c68 6 8a28f1aa76f5c78c0176f7848f685836
8a28f1aa76f5c78c0176f7848ee2582a 6 8a28f1aa76f5c78c0176f7848f1a582f

I need to select RPId in Italic from the line in bolds, ie the first occurence of each Id value,

Upvotes: 0

Views: 80

Answers (2)

Jander
Jander

Reputation: 16

An easy way to go about solving this is to use a PARTITION. https://www.sqltutorial.org/sql-window-functions/sql-partition-by/

The partition acts like a group by and will group each sequence of id's. The sequence is reset when a new id is found. ROW_NUMBER() acts as a counter so the first one in the sequence will be marked as 1,2,3 and so on. When you take the final result you will look for a RowNumber=1 to only return the first result found for each grouping. The group order is determined by the ORDER BY statement in the partition.

WITH tempsub AS(
    select  AM.Id
    , convert(int,sub.Version) as SubVersion
    , RP.Id as RPId 
    , ROW_NUMBER() OVER (PARTITION BY AM.Id ORDER BY SubVers ASC) AS RowNumber
    from sub 
    left join ZRP as RP on RP.sid=sub.id
    left join ZRP as AM on AM.Id=RP.AmId
    where SUB.Name='xxxx' and AmId<>''
)
SELECT *
FROM tempsub
WHERE RowNumber = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271231

One method (which is not the fastest) but doesn't require subqueries is to use a with ties trick:

select top (1) with ties AM.Id, convert(int,sub.Version) as SubVersion, RP.Id as RPId 
from sub left join
     ZRP RP
     on RP.sid = sub.id left join
     ZRP AM
     on AM.Id = RP.AmId
where SUB.Name = 'xxxx' and AmId <> ''
order by row_number() over (partition by AM.Id order by convert(int, SubVersion));

It seems odd that you are using left join and then focusing on columns from the last table. I suspect that inner join might be sufficient for your purposes.

Upvotes: 4

Related Questions