(SQLite) Setup a query to get Max Child

I don’t know SQL well and I can’t figure how to do this and I whooping someone could tell me how to structure this query:

Say you have a 1 to many relationship between Table1 and Table2

Table1
Key0
Field1
Field2

Table2
Key0
Table1Key0
NumericValue
FieldOfInterest

What want is a query that returns one record for Table1 record along with one ValueOfInterest from table 2 for ONLY the child with the greatest NumericValue

I tried
SELECT Table1.*, Max(Table2.NumericValue), Table2 FROM Table1, Table2 WHERE Table1.Key0 = Table2.Table1Key0

But that only returns one record because Max is looking at all of table 2 first…

I think I need to use a subselect but I can’t figure out how to structure it.

Thanks,

  • Karen

Try this:

select Table1.Key0, Table1.Field1, Table1.Field2 (select Max(Table2.NumericValue) from Table2 where Table2.TableKey0 = Table1.Key0) MaxNum from Table1

Hi Simon,
As written that does not work… I have to have “From Table1, Table2” at the end.
That looks like I’m getting Table1 X table2 records.

I think Simon has a typo

select Table1.Key0, Table1.Field1, (select Max(Table2.NumericValue) from Table2 where Table2.TableKey0 = Table1.Key0) as MaxNum from Table1

you CAN run a subquery in the result list which seems to be what Simon was intending to illustrate

That almost works BUT I don’t need MaxValue, I need FieldOfInterst from the child record with the MaxValue. Sorry I did not make that clear. I missed that when I typed the select statement

Thanks

  • KAren

select
Table1.Key0,
Table1.Field1,
(select FieldOfInterest from Table2
where table2.table1.key0 = Table2.TableKey0 = Table1.Key0
and table2.NumericaValue = (select Max(Table2.NumericValue) from Table2 where Table2.TableKey0 = Table1.Key0)
)
from
Table1

or something like that :stuck_out_tongue:

In SQLite you do not need the AS keyword. I used MaxNum as a field identifier for the calling code.

If you want the maximum of the FieldOfInterest then the code should be:

select Table1.Key0, Table1.Field1, Table1.Field2, (select Max(Table2.FieldOfInterest) from Table2 where Table2.TableKey0 = Table1.Key0) MaxNum from Table1

[quote=187665:@Norman Palardy]select
Table1.Key0,
Table1.Field1,
(select FieldOfInterest from Table2
where table2.table1.key0 = Table2.TableKey0 = Table1.Key0
and table2.NumericaValue = (select Max(Table2.NumericValue) from Table2 where Table2.TableKey0 = Table1.Key0)
)
from
Table1

or something like that :P[/quote]

That actually works… though my head hurts looking at it!

Thanks,

  • Karen

[quote=187669:@Simon Berridge]If you want the maximum of the FieldOfInterest then the code should be:

[/quote]

No I want FieldOfInterest for the Child with the maximum NumericValue. Norm’s query works.

SQL looks deceptively simple at first blush, but there can be a LOT of subtlety!!!

Thanks

  • Karen

I must have misunderstood.

Did you need a query that returns an item from Table2 that MATCHES the Table2.FieldOfInterest with Table2.NumericValue?

From what I can see in Norman’s example this is what he seems to be doing.

If you just want the maximum value of the FieldOfInterest that matches the Table1 Key then I still think my code will work.

Can you be a little more descriptive with your request?

[quote=187673:@Simon Berridge]Did you need a query that returns an item from Table2 that MATCHES the Table2.FieldOfInterest with Table2.NumericValue?

[/quote]

I’m not sure what you are saying.

For each entry in Table1 I want the FieldOfInterest from that item’s child record with the largest NumericValue. That is what Norm’s code does.

  • Karen

Got it now.

I’m still a bit confused with Norman’s code and will work on it a bit more.

Try this:

select Table1.Key0, Table1.Field1, Table1.Field2, (select Table2.FieldOfInterest from Table2 where Table2.NumericValue = (select Max(T2.NumericValue) from Table2 T2 where T2.Table1Key0 = Table1.Key0)) TheReturnedValue from Table1

Except for the nested table alias you wrote what I wrote :stuck_out_tongue:

I tend to include AS just for clarity

That works

I’m glad we are on the same page, Norman.

I couldn’t understand why you had two equals in your query, that is what confused me.

Also, I didn’t really understand Karen’s original question and you did, so that left me a bit confused too. Glad that it is sorted now.

Simon.

I had to reread her post a couple times to figure out why your original post wasnt doing what was asked too :slight_smile:

Super!