MySQL to MSSQL Conversion

[chromis]

Hi,

I wouldn't post such a lazy question usually but I've got to have this sorted for the end of the day, sorry! Could someone tell me how to write this statement in MSSQL please?

[code]
SELECT scores.id, scores.name, scores.email, scores.time, scores.milliseconds
FROM scores
INNER JOIN
(
SELECT T2.id, T2.name, T2.email, min(time_to_sec(T2.time)*1000+T2.milliseconds) AS mintime
FROM scores T2 GROUP BY T2.name, T2.email
) T3 ON scores.name = T3.name AND scores.email = T3.email
AND (time_to_sec(scores.time)*1000+scores.milliseconds ) = T3.mintime
ORDER BY T3.mintime LIMIT 10
{/code]

What the query should return is a recordset of unique names and their highest score based on the time and milliseconds columns in the db.

Thanks!

Chromis

[chromis]

Ok it just needed a bit of adjustment:

SELECT   SCORES.ID,
         SCORES.NAME,
         SCORES.EMAIL,
         SCORES.USERTIME
FROM     SCORES
         INNER JOIN (SELECT   T2.NAME,
                              T2.EMAIL,
                              MIN(USERTIME)  AS MINTIME
                     FROM     SCORES T2
                     GROUP BY T2.NAME,T2.EMAIL) T3
           ON SCORES.NAME = T3.NAME
              AND SCORES.EMAIL = T3.EMAIL
              AND SCORES.USERTIME = T3.MINTIME
ORDER BY T3.MINTIME
 

Also for anyone interested theres a nice SQL formatter here