Hi Mike,
Thanks for the help.
I'm not sure that's the right set of subqueries though; looking at the
generated SQL, the result seems closer to:
CREATE TABLE #B_temp AS (SELECT * FROM B WHERE id IN (SELECT max(number)
mn FROM B GROUP BY A_id))
CREATE TABLE #C_temp AS (SELECT * FROM C WHERE
Hi Mike,
I've simplified the query I'm looking for into a one line: (not sure why I
didn't think of HAVING earlier -- definitely making a mountain from
molehills)
SELECT A.*, B1.*, C1.* FROM A LEFT OUTER JOIN (SELECT * FROM B GROUP BY
A_id HAVING number = max(number)) AS B1 ON A.id = B1.A_id
On Mar 12, 2014, at 2:07 PM, Michael Weylandt michael.weyla...@gmail.com
wrote:
Hi Mike,
I've simplified the query I'm looking for into a one line: (not sure why I
didn't think of HAVING earlier -- definitely making a mountain from molehills)
SELECT A.*, B1.*, C1.* FROM A LEFT OUTER
Sorry for the barrage, Mike. I didn't check my code worked before I posted
(shame on me). I'm looking for:
CREATE TABLE #B_temp AS (SELECT * FROM B WHERE id IN (SELECT id FROM B
JOIN (SELECT max(number) mn FROM B GROUP BY A_id) AS max ON max.mn =
B.number));
CREATE TABLE #C_temp AS (SELECT *
OK subqueries like this:
bsubq =
session.query(B).filter(B.id.in_(session.query(func.max(B.number)).group_by(B.a_id).as_scalar())).subquery()
csubq =
session.query(C).filter(C.id.in_(session.query(func.max(C.number)).group_by(C.a_id).as_scalar())).subquery()
select them all like this:
q =
Database wizards,
I've got a situation where I have one table with multiple one-to-many
mappings (i.e., it is the one in a one-to-many with more than one other
table).
For each row of the one, I want to group each of the manys by some
column and do an outer join between the one and the
Seems SQLite doesn't like the SELECT INTO construct.
This works
CREATE TABLE #B_temp AS SELECT id, A_id, word, number FROM B WHERE id IN
(SELECT MAX(id) FROM B GROUP BY A_id);
CREATE TABLE #C_temp AS SELECT id, A_id, word, number FROM C WHERE id IN
(SELECT MAX(id) FROM C GROUP BY A_id);
I think I'm looking for something like this:
CREATE TABLE #B_temp(id INTEGER, A_id INTEGER, word VARCHAR, number
INTEGER);
CREATE TABLE #C_temp(id INTEGER, A_id INTEGER, word VARCHAR, number
INTEGER);
SELECT * FROM B WHERE id IN (SELECT MAX(id) FROM B GROUP BY A_id) INTO
#B_temp;
SELECT *
can you express the exact query you want in SQL? I can translate to that
easily if you have it. Otherwise if you're looking to figure out what the SQL
would be I'd have to find time to look more closely.
On Mar 11, 2014, at 7:29 PM, Michael Weylandt michael.weyla...@gmail.com
wrote:
