Displaying Unique Dates (Ignoring the Year)

[TastyWheat]

This is probably a really simple query but I'm out of practice on SQL. I have a set of reservations and I need to get each email and any associated reservation dates, but I need to cut out dates that fall on the same day of the year.

I've gotten my query to generate this so far:

Email                | MonthDay
-------------------------------
jane_doe@hotmail.com | 07/07
jane_doe@hotmail.com | 11/04
[COLOR="Red"]john_doe@hotmail.com | 01/22
john_doe@hotmail.com | 01/22[/COLOR]

So I almost have what I want but I still get non-unique rows which is really what I'm shooting for. Yes, I tried DISTINCTROW but it doesn't change the output at all.

Here's my query which gives me output similar to that above:

SELECT DISTINCTROW TBL1.Email, TBL1.MonthDay
FROM (
   SELECT Reservation.Email, Format([When],"mm/dd") AS MonthDay
   FROM Reservation
   INNER JOIN [Time]
   ON Reservation.RID = Time.ReservationID
   WHERE (Reservation.Email Is Not Null)
) AS TBL1;

 

[pbaldy]

Try changing DISTINCTROW to DISTINCT.

 

[TastyWheat]

That did the trick! I'm very surprised. I didn't even consider that because I assumed it would eliminate any duplicates within a given column (not what I was looking for).

I thought of this query earlier this morning and I was surprised it worked too because I thought you needed to use an aggregate function to use GROUP BY:

SELECT Reservation.Email, Format([When],"MM/dd") AS MonthDay
FROM Reservation
LEFT JOIN [Time]
ON Reservation.RID = Time.ReservationID
WHERE (Reservation.Email Is Not Null) AND (Time.[When] Is Not Null)
GROUP BY Reservation.Email, Format([When],"MM/dd");

 

[pbaldy]

DISTINCT operates on the fields in the SELECT clause, DISTINCTROW operates on all fields in the data. Using GROUP BY is also a good solution, and as you discovered you don't need to aggregate on anything.