I have two tables, in PostgreSQL if that matters, with one to many relations. I need to join them so that for each "one" I only get single result from the "many" table. Not only that but I need to single out specific results from the "many" table.
TABLE_A ID | NAME | DATE | MORE COLS.... 1 | JOHN | 2012-01-10 | .... 2 | LIZA | 2012-01-10 | .... 3 | ANNY | 2012-01-10 | .... 4 | JAMES | 2012-01-10 | .... ... TABLE_B ID | CODE1 | CODE2 | SORT 1 | 04020 | 85003 | 1 1 | 04030 | 85002 | 4 2 | 81000 | 80703 | 1 3 | 87010 | 80102 | 4 3 | 87010 | 84701 | 5 4 | 04810 | 85003 | 1 4 | 04030 | 85002 | 4 4 | 04020 | 85003 | 1 ... QUERY RESULT ID | NAME | DATE | CODE1 | CODE2 1 | JOHN | 2012-01-10 | 04020 | 85003 2 | LIZA | 2012-01-10 | 81000 | 80703 3 | ANNY | 2012-01-10 | 87010 | 80102 4 | JAMES | 2012-01-10 | 04810 | 85003 ...
The SORT column in TABLE_B is actually the last char in CODE2 reordered. CODE2 can end with 1-9 but 3 is most important then 5, 7, 4, 2, 1, 0, 6, 8, 9 hence 3-->1, 5-->2, 7-->3 and so forth.
The problem I'm facing is that I need the row from TABLE_B where sort is the lowest number. In some cases there are multiple lowest case (see ID=4 in TABLE_B) then it doesn't matter which of the rows with lowest ID are selected, only that there is single result for that ID.
Simpler, shorter, faster with PostgreSQL's DISTINCT ON:
SELECT DISTINCT ON (a.id)
a.id, a.name, a.date, b.code1, b.code2
FROM table_a a
LEFT JOIN table_b b USING (id)
ORDER BY a.id, b.sort;
Details, explanation, benchmark and links:
I use a LEFT JOIN, so that rows from table_a without any matching row in table_b are not dropped. Resulting NULL values in b.sort sort last by default.
Aside:
While being allowed in Postgres, it's unwise to use date as column name. It's a reserved word in every SQL standard and a type name in Postgres.