SQLite Problem

[jpvniekerk]

I am trying to come to grips with SQL (SQLite).

I have a table as shown in the attachment.

I want to extract the data that is indicated with a red tick.

Essentially what I want is a list of unique BirdID's showing the one with the oldest date, and showing all the fields.

If I use "SELECT *..." I get all the records and fields as shown.

If I use "SELECT DISTINCT BirdID" I only get the BirdID column.

If I use "SELECT DISTINCT BirdID, TickID, etc..." I get all the records again (as shown).

Is there any way I can get the data needed (the records with the red ticks) with an SQL statement?

 

[Inman]

Try this:

SELECT BirdID, TickID, etc... FROM <tablename> GROUP BY Location HAVING (COUNT(Location)=1)

 

[jpvniekerk]

Inman, Thanks for the quick reply.

Your suggestion (modified for the fields I am interested in) Did not quite give me what I want, but put me on the right track.

If I use "SELECT * from AllTicks GROUP BY BirdID", I get all the unique BirdID's. Now I just have to figure out how to make sure that I get those records with the oldest date.

 

[Inman]

You can include ORDER BY and ASC or DESC to sort that column

 

[jpvniekerk]

Inman, Thanks - got it working perfectly now - even got it working with nested commands!

My final SQL looks like this:
SELECT * FROM (SELECT TickList.TickID, TickList.BirdID, BirdList.CommonName as BirdName, TickList.DateTime, TickList.Trip, TickList.Location FROM TickList JOIN BirdList ON TickList.BirdID = BirdList.BirdID ORDER BY TickList.DateTime DESC) GROUP BY BirdID ORDER BY BirdName

I can now change the last "ORDER BY" to give me date, birdname, tickID, etc. JUST WHAT I WANTED!

Thanks again for your help.