Mark Mark - 5 months ago 11
SQL Question

Return unique row when using GROUP BY based on a column value

Alright, lets say there's 3 tables:
Calendar, Users and Requests.

Calendar.ID, Users.ID and requests.ID are all unique. (If that matters)

I'm trying to pull everything from the dbo.Calendar plus users.color and requests.Status

SELECT calendar.ID, calendar.date, calendar.type, calendar.userID, users.color, requests.status
FROM calendar LEFT OUTER JOIN
requests ON calendar.ID = requests.calendarID LEFT OUTER JOIN
users ON calendar.userID = users.userID
WHERE(calendar.date >= '2016-06-01') AND (calendar.date <= '2016-12-30')
group by calendar.id, calendar.date, calendar.type, calendar.userID, users.color, requests.status


The problem i'm facing is if there's a completed request I get a duplicate row but in this case it should only display "pending".

ID date type userID color status
4 2016-06-16 RE A #cc99ff pending
5 2016-06-17 RE A #cc99ff accepted
5 2016-06-17 RE A #cc99ff pending
6 2016-06-13 LM B #ffff99 NULL
7 2016-06-13 LM B #ffff99 accepted


I'm trying to get this:

ID date type userID color status
4 2016-06-16 RE A #cc99ff pending
5 2016-06-17 RE A #cc99ff pending
6 2016-06-13 LM B #ffff99 NULL
7 2016-06-13 LM B #ffff99 accepted


FYI I haven't touched SQL in over 8 years, this is just a personal project i'm working on.

Answer

You seem to want one row per id and user, but prioritized:

SELECT *
FROM (SELECT c.ID, c.date, c.type, u.userID, u.color, r.status,
             ROW_NUMBER() OVER (PARTITION BY c.ID, u.userID
                                ORDER BY (CASE WHEN r.status = 'Accepted' then 1  
                                               ELSE 2
                                          END) desc
                               ) as seqnum
      FROM calendar c LEFT OUTER JOIN
           requests r
           ON c.ID = r.calendarID LEFT OUTER JOIN
           users u
           ON c.userID = u.userID
      WHERE (c.date >= '2016-06-01') AND (c.date <= '2016-12-30')
    ) rcu
WHERE rownum = 1;