Sqlite2: Joining max values per column from another table (subquery reference)?

Oh, well - that wasn't trivial at all, but here is a solution.

Oh, well - that wasn't trivial at all, but here is a solution: sqlite> SELECT * FROM names AS n1 LEFT OUTER JOIN ( SELECT d1. * FROM datas AS d1, ( SELECT max(countdata) as countdata,name_id FROM datas GROUP BY name_id ) AS ttemp WHERE d1. Name_id = ttemp.

Name_id AND d1. Countdata = ttemp. Countdata ) AS p1 ON n1.

N_id=p1. Name_id; n1. N n1.Name p1.

D_id p1. Name_id p1. Countdata ---- ------------ ---------- ---------- ----------------------------------- 1 nameA 2 1 47 2 nameB 5 2 87 Well, hope this ends up helping someone, :) Cheers!

Notes: note that just calling max(countdata) screws up competely d_id: sqlite> select d_id,name_id,max(countdata) as countdata from datas group by name_id; d_id name_id countdata ---- ------------ ---------- 3 2 87 1 1 47 so to get correct corresponding d_id, we must do max() on datas separately - and then perform sort of an intersect with the full datas (except that intersect in sqlite requires that there are equal number of columns in both datasets, which is not the case here - and even if we made it that way, as seen above d_id will be wrong, so intersect will not work). One way to do that is in using a sort of a temporary table, and then utilize a multiple table SELECT query so as to set conditions between full datas and the subset returned via max(countdata), as shown below: sqlite> CREATE TABLE ttemp AS SELECT max(countdata) as countdata,name_id FROM datas GROUP BY name_id; sqlite> SELECT d1. *, ttemp.

* FROM datas AS d1, ttemp WHERE d1. Name_id = ttemp. Name_id AND d1.

Countdata = ttemp. Countdata; d1. D d1.

Name_id d1. Countda ttemp. Coun ttemp.

Name_id ---- ------------ ---------- ---------- ----------------------------------- 2 1 47 47 1 5 2 87 87 2 sqlite> DROP TABLE ttemp; or, we can rewrite the above so a SELECT subquery (sub-select? ) is used, like this: sqlite> SELECT d1. * FROM datas AS d1, (SELECT max(countdata) as countdata,name_id FROM datas GROUP BY name_id) AS ttemp WHERE d1.

Name_id = ttemp. Name_id AND d1. Countdata = ttemp.

Countdata; d1. D d1. Name_id d1.

Countda ---- ------------ ---------- 2 1 47 5 2 87.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.