Reputation: 195
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
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
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