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

You need to do this much sooner. Do it in the innermost SELECT, so you're choosing the maximum date only from the relevant records. Don't forget to use the right local alias.

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

This is to get additional fields into the final result? If so, you should add this at the end as another join to the main table.

Tamar