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

It matters which query you're putting this WHERE clause on. It needs to go on the innermost query because that's where you want to rule out all the records that aren't in your date range.

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 ;
                                    where MyTable.date_open >= '20110101' and MyTable.date_open <= '20221231') csrMaxDate ;
                          ON MTB.DateFld = csrMaxDate.MaxDate) csrMaxOrdNo ;
         ON MyTable.ORDER_NO = csrMaxOrdNo.MaxOrd

This way, you are selecting the maximum date field you have within that range. If you add WHERE at the end, you're already only looking at records for the maximum date field in the whole data set.

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

MTB is just an alias for MyTable here and it has Date_Fld (or maybe you were worried about my type of that field as DateFld--I don't use hyphens in field names, so got it wrong).

Tamar