It is possible to fetch with a single SQL the minimum (maximum) row in the table when the compared value is combined of two separate fields?

Question

Dear ladies and sirs.

Examine the following scenario. There is a table with two columns - DateWithoutMilliseconds and Milliseconds. Together these two columns define the exact timestamp with the milliseconds.

Is it possible to write a single SQL statement (not using stored procedures) which would fetch the row with the minimum timestamp with the milliseconds precision? Actually, just the timestamp is good enough, but it has to be with the milliseconds, because several rows may have the same DateWithoutMilliseconds value, but differ in milliseconds.

Now, I am complete with the idea of storing the total milliseconds in the database instead of the aforementioned two columns. However, I must be sure that I am not missing anything and if there is an SQL that allows to have two columns and still be able to fetch the exact minimum - I want to know it.

Thanks a lot in advance to all the good samaritans out there.

EDIT

We use both SqlServer and MySql.

Answer 1

It will probably look different for different databases, but in SQL Server you should be able to do something like this:

SELECT TOP 1 
    Field1, Field2, 
    dateadd(ms, MillisecondsField, DateWithoutMillisecondsField) TimeStamp
FROM Table
ORDER BY TimeStamp DESC

Answer 2

In SQL Server you don't need to specify the time fields in the SELECT clause. You can write it like this:

SELECT TOP 1
       field1
     , field2
  FROM table
 ORDER BY DateWithoutMilliseconds ASC
     , Milliseconds ASC;

I am assuming that when you say you want the minimum timestamp, you want the earliest date. If you want the latest date, then replace ASC with DESC in the ORDER BY clause.