Display only one record per criteria

[amygr063]

I realize this question has been probably asked a 1000 times but after
reading through and trying some of the examples here I still can't seem
to get my query to work.

I have 3 columns , date, time and count - > I want to display only one
count (the maximum) by day which can have several records depending on
the time. When I display the date and Max(count) descending, it works
perfectly. But when I add the column Time it takes the "Group By" total
and displays all the records found for the day (see example below). I
only want to display the first record found for each day (sorted by
date asc and count desc) - how do I ignore the remaining records for
the same day? Thanks so much - Amy.

date time count
1/1/2006 12:00 47
1/1/2006 18:00 28
1/1/2006 9:00 9
1/2/2006 9:00 49
1/2/2006 18:00 44
1/2/2006 12:00 10
1/4/2006 12:00 45
1/4/2006 18:00 6
1/4/2006 9:00 2
1/5/2006 9:00 20
1/5/2006 18:00 16
1/5/2006 12:00 3
1/6/2006 9:00 77
1/6/2006 12:00 8

 

[mdiazfreire]

I don't know if this works for MS Access (I use it in PostgreSQL)

select distinct on (date) date,time,count ...

That makes the query only return one row for each unique date, even
when that rows that have the same date don't have the same values for
the other columns (that is where this clause differs from the standard
DISTINCT, this one only works when the rows have the same value in all
the columns).

If it doesn't work I think you will have to use subqueries or to make a
new query which uses the one you already have. That new query should be
something like SELECT date, max(count) FROM "your query" GROUP BY date.
But I think that in this case it won't allow you to use the "time"
field...

 

[amygr063]

Thanks for responding. I tried before to use the DISTINCT and I just
couldn't get it to work..trying now to make it work with 2 queries -
we'll see.
Amy