0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for Ubuntu users and developers

WITH MultiBadgeHolders AS 
(
 SELECT UserId,Name,Date From Badges
 WHERE UserId IN 
 (
  SELECT UserId FROM Badges WHERE Name='##BadgeName1##'
  INTERSECT
  SELECT UserId FROM Badges WHERE Name='##BadgeName2##'
  INTERSECT
  SELECT UserId FROM Badges WHERE Name='##BadgeName3##'
 )
 AND Name IN ('##BadgeName1##','##BadgeName2##','##BadgeName3##') 
),
AllHoldersBadges AS 
(
 SELECT *, ROW_NUMBER() OVER (PARTITION BY UserId,Name ORDER BY Date ASC) AS Row 
 FROM MultiBadgeHolders
),
Results AS
(
 SELECT UserId,Name,Date, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) AS Row 
 FROM AllHoldersBadges WHERE AllHoldersBadges.Row=1
)
SELECT UserId AS [User Link],Name AS 'Last Badge Name',Date 
FROM Results 
WHERE Results.Row=1
ORDER BY Date ASC

view current version

edited sep 2 13

created sep 2 13 n8r0n