Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 11 to 15 of 15 total |
Still confused by SQL behaviour after all these years ... |
Mon, Mar 21 2016 6:43 AM | Permanent Link |
Adam Brett Orixa Systems | Roy & 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 lateral thinking. Thank you both for thinking it through, I'm feeling pretty stupid having seen how straightforwardly it can be solved. I was _so_ close to Richard's solution in my head but just couldn't get there. I need this form of SQL pretty frequently, and have used all sorts of work-arounds in the past. It is the JOIN between the SELECT and SUB-SELECT that does the magic. |
Mon, Mar 21 2016 8:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
I thought doing it your way without the GROUP BY clause would work but I just tried it here on a table with c4500 rows. It seemed to work well when I used it with the primary key as Col1 (c3.5 seconds) Trying it with a different indexed integer column which is not unique is not as satisfactory > 1000 seconds The UDF approach isn't exactly speed at c17 seconds There are also differences - UDF produces 83 rows yours 85 I've added an ORDER BY clause so I can try and compare - more in another 1000 seconds or so Roy Lambert |
Mon, Mar 21 2016 10:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Richard
Found the other problem - if you have two where MAX(whatever) is the same you get two rows in the result set. Roy Lambert |
Mon, Mar 21 2016 10:33 AM | Permanent Link |
Raul Team Elevate | On 3/16/2016 8:12 AM, Adam Brett wrote:
> 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. The other option that i have used (in addition to one Richard proposed) is to use an outer join and use the nulled columns as where clause. Note that you will need a autoinc or similar uniwue row column in either case if you multiple max values. In some cases i found this to be faster since only single join happens (instead of potentially many sub selects) Something like this : SELECT A.* FROM sqltest A LEFT OUTER JOIN sqltest B ON (A.col1 = B.col1) AND ((A.Col3 > B.Col3) OR (A.col3 = B.col3 AND A.RecNO < B.RecNO)) WHERE B.Col1 IS NULL; Raul |
Mon, Mar 21 2016 12:53 PM | Permanent Link |
Adam Brett Orixa Systems | >>Note that you will need a autoinc or similar unique row column in either
>>case if you multiple max values. Definitely. I quite often need this syntax where the MAX()-ed column is a timestamp, so the "MAX" should always work (I don't need sub-milli-second accuracy!) Otherwise adding some sort of counter / autoinc would do it. There is an issue of speed. Indexing can help, I am also going to look to put the referenced "list of MAX" in a VIEW, and see whether this improves performance. |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |