Select 1 record per group
-
Have a table with 4 columns- Id (int), CustomerId (int), UserId (int), Url (varchar). For each Id there are some records with different (or equal) CustomerId/UserId. Need to select 1 Url per each Id using this algorithm- if Max(CustomerId) exists for this Id group- use it to select Url. If Max(CustomerId) doesn’t exist (all CustomerId are equal) use Min(UserId) to select Result. How to do this select in elegant/simple way? Thanks
-
This querry treats the cases where (all CustomerId are equal) or the MAX row is unique (I don't have 2 rows with MAX and some other with smaller values)
Select b.*
from
(Select ID, MAX(CustomerId) maxID,MIN(CustomerId) minID, MIN(UserID) UserID FROM MyTable ) a
INNER JOIN MyTable b ON a.ID=b.ID
WHERE
1=Case
when maxID=minId && a.UserId=b.UserId then 1
when maxID>minID && maxID=b.CustomerID then 1
else 0
end
Kindest Regards,Vasc
-
Unfortunately, there is an error in your query and I can't fix it as, frankly, I didn't get the logic in "WHERE" part, i.e. 1=Case, etc. It shoul be in this way: if maxId=minId then use Id + UserId to get Url else use Id + maxId to get Url. Could you, please, provide more details. Thanks
-
Does this correct the error and gives the expected output?
Select b.*
from
(Select ID, MAX(CustomerId) maxID,MIN(CustomerId) minID, MIN(UserID) UserID FROM MyTable ) a
INNER JOIN MyTable b ON a.ID=b.ID
WHERE
1=Case
when maxID=minId and a.UserId=b.UserId then 1
when maxID>minID and maxID=b.CustomerID then 1
else 0
end
-
nice and && : ))
I looked like 5 min to see the diff ....: )
Kindest Regards,Vasc
-
Hehe, those 30 hours of C++ are finally paying off
. -
when maxID=minId and a.UserId=b.UserId then 1
--if there is no MAX(all are equal) than check to see
if UserID is equal (ID group is catched in join!)
when maxID>minID and maxID=b.CustomerID then 1
--if there is a MAX (min<>max I have diff data values) than check if this Max is equal to CustomerID
Kindest Regards,Vasc
-
Thanks a lot, guys. It 's working now (Group By Id was missed either). More important, I hope I understood how it works. Thanks
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply