Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 15 total |
Still confused by SQL behaviour after all these years ... |
Wed, Mar 16 2016 8:12 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Wed, Mar 16 2016 8:48 AM | Permanent Link |
Walter Matte Tactical Business Corporation | 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 |
Wed, Mar 16 2016 8:51 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Mar 16 2016 9:34 AM | Permanent Link |
Adam Brett Orixa Systems | 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? |
Wed, Mar 16 2016 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, Mar 16 2016 12:19 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Mar 17 2016 8:33 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Mar 17 2016 9:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Mar 20 2016 5:30 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Mon, Mar 21 2016 2:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |