Is it possible to perform join on the same table

Databases
1

Not sure if this is possible or if there is a better way of doing this.

I have a table which I want to perform a distinct statement on the field name event. This field will contain duplicate events but the date will be different

Event ! Dte ! Location
a ! 1/1/2015 ! aa
a ! 1/2/2015 ! aa
a ! 1/3/2015 ! bb
b ! 1/2/2015 ! cc
b ! 1/3/2015 ! cc

I would like to return
Event dte location
a ! 1/1/2015 ! aa
b ! 1/2/2015 ! cc

I have tried group by, but get aggregate errors when I apply the order by dte .

Is it possible to select the distinct field and then join the other fields from the same table

I dont have any code available as I am not sure on the best way forward with this. Would appreciate any help to find a solution

2

And SELECT Event, Dte, Location FROM TableName doesn’t work?

3

That pulls all the records and does not filter the event column to filter out the duplicates

4

Since it looks like you only want to see the very first event of the same type…

Use MIN() on the date. That will give you as a result the very first event

SELECT Event, MIN(Dte), Location FROM TableName
1 Like
5

Thank Molona tried that earlier myself and again now. It throws up and error you tried to execute a query that does not include the specified expression ‘event’ as part of a aggregate function.

6

To use Min, you need to use GROUP BY

SELECT event, MIN(date), location FROM table name GROUP BY event, location

1 Like
7

Many thanks Dave did that and it worked thank you all for your imput

8

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