SQL Max(Date) query could return multiple rows

Question

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?

Answer 1

Try something like this:

select top(1) with ties *
from MyTable
where PART_CAT_CODE = 'BILL'
order by eff_date desc