I have this table i mimer:

ID NumberID Time
1 1 1000
2 1 1000
3 1 900
4 2 950

I want to SELECT the ID with different NumberID and highest Time, but only one per Number ID.

My SELECT is as follows:

SELECT ID FROM Table AS T1
WHERE
(T1.Time= (
SELECT MAX (T2.Time) FROM Table AS T2
WHERE T2.NumberID = T1.NumberID))

this returns ID: 1, 2 and 4.

I want ta have a SELECT which returns 1 or 2 (doesn´t matter which one) and 4.

My problem is that neither FIRST or TOP works in Mimer, does anyone have a work around for that?

Materialize all three columns, then GROUP BY NumberID and apply either Min() or Max() to ID.

-PatP

I don´t understand what you mean. I tried to use GROUP BY but I couldn´t get it to work because it complaines about ID not groupable. Could you give some example code?

I tried

SELECT ID, NumberID FROM Table AS T1
WHERE
(T1.Time= (
SELECT MAX (T2.Time) FROM Table AS T2
WHERE T2.NumberID = T1.NumberID))
GROUP BY NumberID

but this doesn´t work. Error message: Column ID not referenced in GROUP BY clause

Keep in mind that I don't have a copy of Mimer here to test this, but I'm pretty sure that you can use:-PatP

I understand and it works. Thank you for the help.