CODEWITHSUNDEEP
sqlsql-servergrouping
marianopicco
marianopicco

Reputation: 83

Group By showing duplicates

I'm trying to select rows for this meetings application we're doing. Meetings have one or more Agenda Items, these Agenda Items have an EventNumber (which is 0 if no event is linked, or an integer if there is)

I want to select only one row per meeting, but I get more than one if for the same meeting we have maybe an Agenda item with a EventNumber of 0, and one different than 0. Just need the know if for that whole meeting, at least one of the AgendaItems has an EventNumber different than zero.

The query is almost there. It works when a meeting has only AgendaItems with EventNumbers = 0, but it brings back duplicates when a meeting has a mix of both.

Tried adding a HAVING COUNT(*) > 1 after the Grouping, or saying MAX(MeetingAgendaItem.EventNumber), and applying the DISTNCT keyword after the SELECt.

SELECT
    Meeting.Id, 
    Meeting.Location, 
    Meeting.Name, 
    MeetingAgendaItem.EventNumber,
    Meeting.StartDate,
    Meeting.EndDate

FROM ((Meeting 
    LEFT JOIN MeetingInvitees ON (Meeting.Id = MeetingInvitees.MeetingId))
    LEFT JOIN MeetingAgendaItem ON (Meeting.Id = MeetingAgendaItem.MeetingId))

GROUP BY Meeting.Id, Meeting.Location, Meeting.Name, MeetingAgendaItem.EventNumber, Meeting.StartDate, Meeting.EndDate

ORDER BY Meeting.Id

Id  Location Name                   Meeting Name        EventNumber          StartDate              EndDate
19  Pont des Charrettes, 30700, FR  Meeting Generated   0                    29-06-2019 00:00:00    29-06-2019 23:59:59
19  Pont des Charrettes, 30700, FR  Meeting Generated   8747                 29-06-2019 00:00:00    29-06-2019 23:59:59

Upvotes: 1

Views: 109

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

I would suggest a correlated subquery:

SELECT m.*
       (SELECT MAX(mai.EventNumber)
        FROM MeetingAgendaItem mai
        WHERE m.Id = mai.MeetingId
       ) as max_EventNumber          
FROM Meeting m
ORDER BY m.Id;

Notes:

  • You are not using the invitees table, so I removed it.
  • Table aliases make the query easier to write and to read.
  • The invitees table is one of the causes of duplicates. You want one row per meeting so no outer GROUP BY is needed.
  • This should be much more efficient than your version, because it eliminates unneeded duplicate records and the outer group by.

Upvotes: 0

cnom
cnom

Reputation: 3241

You probably need to add "MeetingAgendaItem.EventNumber > 0" criteria on your join as well:

SELECT
Meeting.Id, 
Meeting.Location, 
Meeting.Name, 
MeetingAgendaItem.EventNumber,
Meeting.StartDate,
Meeting.EndDate

FROM ((Meeting 
LEFT JOIN MeetingInvitees ON (Meeting.Id = MeetingInvitees.MeetingId))
LEFT JOIN MeetingAgendaItem ON (Meeting.Id = MeetingAgendaItem.MeetingId AND MeetingAgendaItem.EventNumber > 0 ))

GROUP BY Meeting.Id, Meeting.Location, Meeting.Name, MeetingAgendaItem.EventNumber, 
Meeting.StartDate, Meeting.EndDate

ORDER BY Meeting.Id

Upvotes: 0

Red Devil
Red Devil

Reputation: 2393

This should work:

select * from (
SELECT
    ROW_NUMBER() over (partition by Meeting.Id,Meeting.Location,Meeting.Name, Meeting.StartDate,Meeting.EndDate order by Meeting.Location) rn,
    Meeting.Id, 
    Meeting.Location, 
    Meeting.Name, 
    MeetingAgendaItem.EventNumber,
    Meeting.StartDate,
    Meeting.EndDate

FROM ((Meeting 
    LEFT JOIN MeetingInvitees ON (Meeting.Id = MeetingInvitees.MeetingId))
    LEFT JOIN MeetingAgendaItem ON (Meeting.Id = MeetingAgendaItem.MeetingId))

GROUP BY Meeting.Id, Meeting.Location, Meeting.Name, MeetingAgendaItem.EventNumber, Meeting.StartDate, Meeting.EndDate

ORDER BY Meeting.Id
) t where rn=1

Upvotes: 0

Nick
Nick

Reputation: 147146

You can work around this by taking MAX(MeetingAgendaItem.EventNumber) and removing it from the GROUP BY. This will give you the largest EventNumber associated with the Agenda item (or 0 if they are all 0), which seems to meet the criteria in your question. Change your query to this:

SELECT
    Meeting.Id, 
    Meeting.Location, 
    Meeting.Name, 
    MAX(MeetingAgendaItem.EventNumber),
    Meeting.StartDate,
    Meeting.EndDate

FROM ((Meeting 
    LEFT JOIN MeetingInvitees ON (Meeting.Id = MeetingInvitees.MeetingId))
    LEFT JOIN MeetingAgendaItem ON (Meeting.Id = MeetingAgendaItem.MeetingId))

GROUP BY Meeting.Id, Meeting.Location, Meeting.Name, Meeting.StartDate, Meeting.EndDate

ORDER BY Meeting.Id

Upvotes: 1

Related Questions