>>>>>>>ADS has a Time datatype, which is internally stored as the number of milliseconds since midnight in a 4-byte integer.
>>>>>>
>>>>>>OK the same query, but you should find the right functions for converting:
>>>>>>
>>>>>>SELECT YourTable.*;
>>>>>> FROM YourTable;
>>>>>>INNER JOIN (SELECT Sh, MAX(ConvertDateToISO(DateSaved)+' '+ConvertTimeToString(TimeSaved)) AS DateTimeSaved;
>>>>>> FROM YourTable;
>>>>>> GROUP BY Sh) Tbl1;
>>>>>>ON YourTable.Sh = Tbl1.Sh AND;
>>>>>> ConvertDateToISO(YourTable.DateSaved)+' '+ConvertTimeToString(YourTable.TimeSaved) = Tbl1.DateTimeSaved
>>>>>>
>>>>>>
>>>>>>Where ConvertDateToISO() should convert your Date to string with folwong format "YYYYMMDD" and
>>>>>> ConvertTimeToString() should convert Time to string no matter what format it is, just make sure that 12:01 PM is bigger than 12:01 AM it could be:
>>>>>>"18:20", "1820", "66000" (that is 18*3600 + 20*60)
>>>>>
>>>>>Hi Borislav,
>>>>>
>>>>>Please check my suggestion. It may be slower, but no conversions are needed. First we filter the maximum date, then the maximum time for that date.
>>>>
>>>>
>>>>I currently have this statement
>>>>
>>>>select TOP 1 *
>>>>from CardDetail
>>>>WHERE CardSN = 4162078351
>>>>order by DateSaved DESC , TimeSaved DESC
>>>>
>>>>This statement is what I need but that is only for one CardSn, I need it for all CardSn in the table
>>>
>>>
>>>Try Naomi's suggestion, it should work w/o any conversion. I just didn't read it right.
>>>That should give you want you want. Max record based on both date and time fields.
>>>Just hope ADS support such queries.
>>
>>It does not appear that ADS supports this query.
>>I am thinking about reading the ADS table into .NET and doing the processing in .NET instead.
>
>Then ADS didn't support derived tables. If so my query wouldn't work as well.
I would have to write my own date-to-string and time-to-string conversion, and you are right it would not have been guaranteed to work either.
Semper ubi sub ubi.