Is it possible to perform join on the same table

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

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

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

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

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.

To use Min, you need to use GROUP BY

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

1 Like

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

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