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

Your example should work. But it does not. That is, no rows are selected.
Also, I need to add two clauses:
1. Where clause for the MyTable. That is, only rows between some two dates should be considered. So, I added the where at the end of your example. That is, right after the csrMaxOrdNo.MaxOrd I added

where MyTable.DateFld > '20210101' and MyTable.rec_id <> 'D'  && example

2. I also need to have another JOIN to another table. Example of the join:

JOIN MyTable2 on MyTable2.id_number = MyTable.id_number and MyTable2.site_no = 23  && 23 is an example

Thank you very much for your help.