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.

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

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

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

>>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.