dimanche 11 octobre 2015

sqlite, order by date/integer in joined table

I have two tables

Names

id | name
---------
5  | bill
15 | bob
10 | nancy

Entries

id | name_id | added    | description
----------------------------------
2  | 5       | 20140908 | i added this
4  | 5       | 20140910 | added later on
9  | 10      | 20140908 | i also added this
1  | 15      | 20140805 | added early on
6  | 5       | 20141015 | late to the party

I'd like to order Names by the first of their numerically-lowest added values in the Entries table, and display the rows from both tables ordered by the added column overall, so results something like:

names.id | names.name | entries.added | entries.description
-----------------------------------------------------------
15       | bob        | 20140805      | added early on
5        | bill       | 20140908      | i added this
10       | nancy      | 20140908      | i also added this

I looked into joins on the first item (e.g. SQL Server: How to Join to first row) but wasn't able to get it to work. Any tips?

Aucun commentaire:

Enregistrer un commentaire