Min record from TWO tables

Thats a great idea

[quote=422247:@Dave S]In reality this is for an app that will analyze a Xojo source code.

Name is the name of a property, method or event, ID is the name of the class it is in.

Value1 identifies the SUBCLASS and Value2 identifes the SUPERCLASS

So if it is ONLY in Value1 then it is a method that is unique to the Subclass
If only in Value2 then it is inherited from the Superclass
and if in both then it is Overridden by the Subclass[/quote]

Just a heads up about this. If it’s a property in a subclass then it shadows the supers - but only when the instance accessing it is of the subclass type (a cast to the super type will access the supers). An event can’t be overidden - but the super can define a new event with the same signature and raise it to pass it down the chain of events. And it gets messier if you have a method that is named the same as a property in the super class.

[quote=422320:@Jeff Tullin]Odd.

If you don’t include ID in the group by clause, Oracle and SQL Server will kick up a fuss.
What does SQLLite do here with these two rows?

10,FRED,1
20,FRED,0

Name = FRED because we group by it
min( base) is 0

, but without an aggregate function on ID, would it return 10 (the right one), or 20 (the wrong one)?

and both Min(ID) and Max(ID) would return the wrong value sometimes… you need the ID associated with the row on which the lowest base exists.[/quote]

Woudn’t 20 be the correct line with min(base)=0?

I’ve noticed MySQL doesn’t like it either. Hmmm.

Standard SQL doesn’t define which ‘row’ to choose for a bare/ungrouped column.
MySQL returns an error. Others may pick a row at random.
Here is why it works with SQLite and this dataset:

[quote]Special processing occurs occurs when the aggregate function is either min() or max(). Example:

  [code]SELECT a, b, max(c) FROM tab1 GROUP BY a;[/code]

When the min() or max() aggregate functions are used in an aggregate query, all bare columns in the result set take values from the input row which also contains the minimum or maximum. So in the query above, the value of the “b” column in the output will be the value of the “b” column in the input row that has the largest “c” value. There is still an ambiguity if two or more of the input rows have the same minimum or maximum value or if the query contains more than one min() and/or max() aggregate function. Only the built-in min() and max() functions work this way.[/quote]

Based on this source data

“100” “Dave” “1”
“110” “Dave” “2”
“120” “Suzy” “2”
“150” “Fred” “1”

Using this Query: SELECT Min(ID) AS MethodID, Name, SUM(Value) As MethodValue FROM Methods GROUP BY Name ORDER BY methodid;

I get this result
“100” “Dave” “3”
“120” “Suzy” “2”
“150” “Fred” “1”

The SQLite database I used is available at Dave.db.

Please note this refers to my previous suggestion where 1 = super, 2 = inherited, 3 = overridden.