CODEWITHSUNDEEP
sqlsql-server
srh
srh

Reputation: 1671

SQL: How to get the row when max is on more than 1 column

I am working with Microsoft SQL Server 2008 R2.

I have a table named employee:

create table employee (
   employee_id bigint not null primary key, 
   first_name varchar(50) not null, 
   middle_name varchar(50) null, 
   last_name varchar(50) not null
)

I have a table named eligibility. It has a FK to employee table. It has a unique key comprise of 3 columns: employee_id + effective_date + sequence_number. 

create table eligibility (
   eligibility_id bigint not null primary key,
   employee_id bigint not null foreign key references employee (employee_id), 
   effective_date date not null,
   sequence_number int not null,
   value varchar(20) not null,
   constraint UK_eligibility unique (employee_id, effective_date, sequence_number)
)

I have 1 row in employee table with employee_id = 1001:

insert into employee (employee_id, first_name, middle_name, last_name) values (1001, 'A', 'B', 'C')

I have 4 rows in eligibility table for the same employee_id:

insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (1, 1001, '2016-04-13', 1, 'NS')
insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (2, 1001, '2016-05-25', 1, 'EX')
insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (3, 1001, '2016-05-25', 2, 'VR')
insert into eligibility (eligibility_id, employee_id, effective_date, sequence_number, value) values (4, 1001, '2016-06-05', 1, 'LS')

From the eligibility table, for a given date I want to get the row with the max (effective_date + sequence_number) combination which is less than or equal to that given date.

Examples: For 2016-04-30 date I would want the row with eligibility_id = 1. For 2016-05-30 date I would want the row with eligibility_id = 3.
For 2016-06-30 date I would want the row with eligibility_id = 4.

I have wrote the query to get the desired results. This is the query for 2016-05-30 date:

select * from eligibility e 
where 
    e.effective_date = (select max(e1.effective_date) 
                            from eligibility e1 
                            where e1.employee_id = e.employee_id and 
                                e1.effective_date <= '2016-05-30') AND 
    e.sequence_number = (select max(e2.sequence_number)
                            from eligibility e2 
                            where e2.employee_id = e.employee_id and 
                                e2.effective_date = e.effective_date)

The query is ok but I want to try write it in some different way to get the same results. What other way you would recommend?

Upvotes: 0

Views: 69

Answers (2)

IVNSTN
IVNSTN

Reputation: 9325

This looks to me like TOP-1 with ties:

SELECT TOP 1 WITH TIES *
FROM eligibility e
WHERE e.effective_date <= '2016-05-30'
ORDER BY e.effective_date DESC, sequence_number DESC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Hmmm, I would use row_number():

select e.*
from (select e.*,
             row_number() over (partition by employee_id order by effective_date desc, sequence_number desc
                               ) as seqnum
      from eligibility e
     ) e
where seqnum = 1;

Upvotes: 3

Related Questions