SQL Max(Date) query could return multiple rows

Joseph Mancini 81 Reputation points
2024-05-13T14:12:50.9366667+00:00

Capture

Having a devil of a time trying to find a query to return what I need. I have a table as above. I am trying to use max(eff_date) to return the last record(s). Most of the time, only 1 record will be returned for the max(eff_date), but sometimes, more than 1 record may be returned. So for my needs, I would need to return the last 2 records from the data. Within this, I also need to make sure that Part_Cat_Code only returns values of 'BILL'. How do I do this?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,894 questions
{count} votes

Accepted answer
  1. Viorel 112.9K Reputation points
    2024-05-13T14:21:24.7033333+00:00

    Try something like this:

    select top(1) with ties *
    from MyTable
    where PART_CAT_CODE = 'BILL'
    order by eff_date desc
    
    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful