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