I am sure this is in the manual somewhere, but I am looking for a bit of human feedback.

Say I have data:

1    xyz    12
1    xxx    13
2    abc    14
3    def     15

I write:

SELECT
Col1, Col2, MAX(Col3)
FROM Table
GROUP BY Col1

I will I _always_ see:

1    xxx    13
2    abc    14
3    def     15

or might I sometimes see

1    xyz    13
2    abc    14
3    def     15

i.e. does the GROUP always select all the values for the selected columns from the row picked by the GROUPING statement (MAX in this case), or might it pick other random rows from a non-grouped column.

Adam:

Because you group by Col1 only - max only acts on that field - NOT THE ROW.  So within the Group(Col1) it gets the Max(Col3) but Col2 can be any of them.

Example - each field is treated separately within the Grouping.

SELECT
Col1, Col2, MAX(Col3), Min(Col4)
FROM Table
GROUP BY Col1

1    xyz    12    3
1    xxx    13    5


The min(Col4)  and the Max(Col3) are different rows - so what would you expect Col2 to be? So if you are grouping - unless you group that field or use a Max, Min , ...etc then the result will be one of the items - any on e of them.

Walter

Adam


Memory says that ignoring the GROUPing column(s) and any aggregate function for what's left  its the first row in physical order that's found.

If I'm right it means that unless, and until you physically reorder the rows (OPTIMIZE) it will be whichever was shoved in there first.

I could be wrong and it could be first found in whatever order you've elected to sort the output but that's what my memory is telling me.

Roy Lambert

Thanks Walter and Roy, that is what I had thought, i.e. there is no real guarantee you will get xyz, you might get xxx.

However there are many situations where you really want to get XYZ. In such cases I am confused about the best options for writing the SQL statement.

Going back to the data.

1    xyz    12
1    xxx    13
2    abc    14
3    def     15

If I absolutely must return

1 xxx 13 (ie. the whole row linked to the highest Col3 in the table)

What would be the best SQL?

I think at very least I would have to add a unique-id row to the table so I could SELECT for these based on the Col3 values, then search for this. Is there an easier way?

Adam


>Thanks Walter and Roy, that is what I had thought, i.e. there is no real guarantee you will get xyz, you might get xxx.
>
>However there are many situations where you really want to get XYZ. In such cases I am confused about the best options for writing the SQL statement.
>
>Going back to the data.
>
>1 xyz 12
>1 xxx 13
>2 abc 14
>3 def 15
>
>If I absolutely must return
>
>1 xxx 13 (ie. the whole row linked to the highest Col3 in the table)
>
>What would be the best SQL?

I think you need some more coffee. Unless I'm very wrong then as I posted above - you can't. I thought I had a solution using sub selects but unless you want to wait for your next birthday its not feasible. About an hour in and its still running.

What might work is a UDF. It should be possible to write one in SQL/PSM - pass in the column 1 and MAX(Col3) values and do a lookup returning the col2 information.

>I think at very least I would have to add a unique-id row to the table so I could SELECT for these based on the Col3 values, then search for this. Is there an easier way?

Even following my suggestion (assuming it works) unless you can guarantee never having two rows where the MAX(Col3) values are the same for a given col1 then you can't guarantee the col2 value.

Roy

ps I could be wrong and someone may have a brilliant idea

Adam


UDF like

CREATE FUNCTION "GetCol2" (IN BoxNo INTEGER, IN MaxSize INTEGER)
RETURNS INTEGER
BEGIN

DECLARE Interim INTEGER;
DECLARE Lookup SENSITIVE CURSOR FOR xLookup;

PREPARE xLookup FROM 'SELECT _MsgNo FROM Bad WHERE _fkMailBoxes = ? AND _Size = ?';

OPEN Lookup USING BoxNo, MaxSize;

FETCH FROM Lookup('_MsgNo') INTO Interim;

RETURN Interim;

END
VERSION 1.00!

Roy Lambert

Roy,

You can run all of that in a single SQL statement:

SELECT _MsgNo INTO :Result FROM Bad WHERE _fkMailBoxes = :BoxNo AND _Size = :MaxSize

(provided that it returns a single row)

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>You can run all of that in a single SQL statement:
>
>SELECT _MsgNo INTO :Result FROM Bad WHERE _fkMailBoxes = :BoxNo AND _Size = :MaxSize

Following that clue I managed to get it down to

CREATE FUNCTION "GetCol2a" (IN "BoxNo" INTEGER, IN "MaxSize" INTEGER)
RETURNS INTEGER
BEGIN

DECLARE Result INTEGER;

EXECUTE IMMEDIATE 'SELECT _MsgNo INTO ? FROM Bad WHERE _fkMailBoxes = ? AND _Size = ?' USING Result, BoxNo,MaxSize;

RETURN Result;

END
VERSION 1.00!

Roy

Adam,

I think this will do what you want.

SELECT col1, col2, col3
 FROM Table1 T1
 WHERE Col3 = (SELECT MAX(Col3) FROM Table1 T2 WHERE T2.col1 = T1.Col1)

Richard

Richard

>SELECT col1, col2, col3
> FROM Table1 T1
> WHERE Col3 = (SELECT MAX(Col3) FROM Table1 T2 WHERE T2.col1 = T1.Col1)

Interesting, nice bit of leteral thinking.

Roy