[thelist] SQL question

[Mattias Thorslund]

Tab Alleman wrote:
> Maybe something like this?:
>
> SELECT
> 	RecordID
> ,	TheDate
> ,	GroupID
> FROM Example e
> WHERE e.RecordID=(
> 	SELECT TOP 1 RecordID
> 	FROM Example e2
> 	WHERE e.GroupID=e2.GroupID
> 	ORDER BY TheDate DESC
> )
> GROUP BY GroupID
>   

That worked - thanks!

The MySQL-proprietary syntax corresponding to the MSSQL-proprietary "TOP
1" is LIMIT 1, but must be added at the end of the query. I didn't
imagine it would work in a sub-query, but it does:

SELECT
    RecordID
,    TheDate
,    GroupID
FROM Example e
WHERE e.RecordID=(
    SELECT RecordID
    FROM Example e2
    WHERE e.GroupID=e2.GroupID
    ORDER BY TheDate DESC
    LIMIT 1
)

The GROUP BY is not necessary any more, I think.

It's good to know there's at least a non-standard way to make this work
(so I can sleep better), but does anyone know of a way of solving this
in "standard SQL"?

Mattias

[snipped my long original post]