SQLite Problem

jpvniekerk

Active Member
Licensed User
Longtime User
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?
 

Attachments

  • Data.PNG
    30.6 KB · Views: 310
Sort by date Sort by votes
I

Inman

Well-Known Member
Licensed User
Longtime User
Try this:

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

jpvniekerk

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

jpvniekerk

Active Member
Licensed User
Longtime User
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.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
Android Question Android and Sqlite version
Replies
5
Views
5K
Mahares
M
  • Question
Android Question Check Device memory to avoid SQLITE Errors.
Replies
1
Views
2K
Quandalle
Q
A
  • Question
Android Question B4XTable- Problem after editing data in SQLite database
Replies
8
Views
2K
anglia
A
  • Question
Android Question Fill Menu With Distinct Sqlite Query Results
Replies
13
Views
5K
RauchG
M
  • Article
B4A Library [Class] Flexible Table
64 65 66
Replies
1K
Views
480K
klaus
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…