Use distinct on col 1 and also return all others cols

Databases
1

I have an access database containing 1 table and need to apply the distinct function which only returns the column selected in the select distinct event statement.

I need to somehow return the other colums and also oder on col 2 which is a date filed dte.

I have tried using group by and group having statement but I get an aggregate error when using the order by.

I am looking for a way to select distinct for the table on event. The join the same table to return the other fields in the same table. Not sure if this is possible. So to recap

table data in table 1
event dte location
Darts

2

It is my understanding that when DISTINCT is used, it applies to all columns selected; so it will find records where the combination of all columns in the select are unique, not just one.

More information can be found here

I’m sure there’s a way to do what you are looking for, but it will likely involve sub-queries.

V/r,

:slight_smile:

3

So, you need to retrieve only one row for each event?
What are the criteria for the rest of the fields?

I guess you need an inner statement for this purpose, such as
WHERE dte = (
SELECT MAX(dte)
etc.
)

4

Hi Graham, could you please include the actual query you’re working on when you post questions like this?
Even if it doesn’t work, it might give us an idea of what you’re trying to achieve and where you’re stuck/mistaken.

5

ok here is the original code but the distinct does not work but it returns the other columns
select distinct event, dte, location
from sports
order by dte

which is the same as
select event, dte, location
from sports
order by dte

other attempts include
select event, min(dte), min(location)
from sports
group by event

which works but does not allow the
order by dte
to function it throws up an error
does not include the specified expression 'dte as part of an aggregate function

6

The last one:

other attempts include
select event, min(dte), min(location)
from sports
group by event
order by min(dte)
1 Like
7

Amit
Many thanks that’s perfect so simple when you take a step back. I have been days working on this thanks again.

8

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.