>>>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?
>>>
>>>TIA
>>>
>>>UPDATE. I think the problem I have is defining the ORDER BY clause correctly. What am I missing?
>>
>>It does look like my approach was overkill (as Tamar correctly said). I changed the SQL Select to simply select records and use a JOIN (not LEFT JOIN). I also ORDER the query on DATE_FLD, ORDER_NO.
>>Therefore, if JOIN finds no records, nothing is selected.
>>If JOIN finds records I select the resulting QUERY and GO BOTTOM. This finds the greatest date and the greatest ORDER_NO record. So, the fields are taken from this record.
>>So far, it works.
>>Let me know if you see anything wrong with approach.
>
>If all you need is one record that has the most recent date and the last order number on that date, this is fine. I understood to mean you needed a bunch of dates and the last order number from each. If you just need one record, then I'd do:
>
>

>SELECT <all the fields you need> ;
>   FROM MyTable ;
>      JOIN (SELECT MAX(ORDER_NO) AS MaxOrd FROM ;
>                    MyTable MTB;
>                       JOIN (SELECT MAX(DATE_FLD) As MaxDate FROM MyTable MTC) csrMaxDate ;
>                          ON MTB.DateFld = csrMaxDate.MaxDate) csrMaxOrdNo ;
>         ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrd
>

>
>IOW, first find the latest date field, then find the max order # for that date, and then choose fields from the record with that order #.
>
>Tamar

Sorry for still not getting this. I went back to your previous suggestion (above).
When I put your code into the SSMS, I do get one record with the max DATE_FLD. Works.
But when I add the following WHERE, no records returned (even though I know there are many in the DB):

where (MyTable.date_open >= '20110101' and MyTable.date_open <= '20221231')

As you can see that give it a fairly large DATE_OPEN range.

UPDATE. I think I found the culprit. I had 2 records where the year for DATE_FLD was 4200. So, somehow, when the range of DATE_OPEN was reasonable, because the DATE_FLD had a very large date, it would return NO records. I still do not understand why.

UPDATE 2. What seems odd to me (and it is me) is that the second JOIN is on the following expression:

ON MTB.DateFld = csrMaxDate.MaxDate

But I don't see anywhere where the query MTB includes the column DateFld.
The MTB is created with the following JOIN:

 JOIN (SELECT MAX(ORDER_NO) AS MaxOrd FROM MyTable MTB;

The above JOIN does not include the column DateFld. And yet your code uses it.
How?