sqlite, order by date/integer in joined table

Question

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 the numerically-lowest added values in the Entries table, and display the rows from both tables ordered by the added column overall, so the results will be 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?

score 1 · Answer 1 · answered Oct 11 '15 at 12:40

1

Give this query a try:

SELECT Names.id, Names.name, Entries.added, Entries.description 
FROM Names 
INNER JOIN Entries 
ON Names.id = Entries.name_id 
ORDER BY Entries.added

Add DESC if you want it in reverse order i.e.: ORDER BY Entries.added DESC.

answered Oct 11 '15 at 12:40

Phantômaxx

37,901
21
84
115

No, because this lists Names multiple times if there are multiple Entries. – frumbert Oct 11 '15 at 22:57
`SELECT DISTINCT` will help... ;) – Phantômaxx Oct 12 '15 at 06:46

score 0 · Accepted Answer · answered Oct 11 '15 at 16:08

0

This should do it:

SELECT n.id, n.name, e.added, e.description 
FROM Names n INNER JOIN
     (SELECT name_id, description, Min(added) FROM Entries GROUP BY name_id, description) e
     ON n.id = e.name_id 
ORDER BY e.added

answered Oct 11 '15 at 16:08

CFreitas

1,647
20
29

sqlite, order by date/integer in joined table

2 Answers2