>>>Hi,
>>>
>>>I am creating a SQL Select that will select ONE record based on two fields. The two fields are DATE_FLD and ORDER_NO field. There could be more than one records with the same DATE_FLD value (e.g. "01/01/2023) but the ORDER_NO will be different for each record. I need to select a record with the largest value in the ORDER_NO field.
>>>
>>>Here is my test:
>>>

>>>select TOP 1 CONVERT(VARCHAR(10),DATE_FLD,101) + STR(ORDER_NO), FIELD2, FIELD3 from MyTable order by DATE_FLD, ORDER_NO DESC
>>>

>>>
>>>Will the above give me what I am looking for?
>>>
>>
>>That's overkill. Use:
>>
>>

>>SELECT DATE_FLD, MAX(ORDER_NO) FROM MyTable GROUP BY DATE_FLD 
>>

>>
>>Tamar
>
>When I started looking "closer" to your suggestion, I see the following problem. The actual case is I need to have other fields selected from the MyTable. And then, I will need to use MAX() on each one of those as well?

If what you're looking for is to have all the other fields come from the record for that date with the highest ORDER_NO, you should look at the OVER clause with the LAST_VALUE function. I wrote about that here: http://www.tomorrowssolutionsllc.com/Articles/Using%20Over%20with%20Analytic%20Functions,%20Part%201.pdf.

What you'd have is something like:

SELECT DATE_FLD, LAST_VALUE(ORDER_NO) OVER (PARTITION BY DATE_FLD ORDER BY ORDER_NO) AS ORDER_NO, ;
             LAST_VALUE(OTHER_FIELD) OVER (PARTITION BY DATE_FLD ORDER BY ORDER_NO) AS OTHER_FIELD, ; 
             ...
   FROM MyTable

Tamar