>>>>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

Thank you for the suggestion and for the link to the article.
Currently I am exploring the option of having a SUBQUERY with EXISTS. So that the SQL Select will select all necessary fields from the table and will use the SUBQUERY to the same table with the code you suggested in the previous reply.
This way, if I understand it correctly, NO record will be selected if SUBQUERY finds no record (if a subquery on group by and JOIN finds no records). Otherwise, the main query will return one record.
I think :)