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

Question

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?

What characteristics of your query do you hope to improve by rewriting it? "some different way to get the same results" affords infinitely many possibilities. — John Bollinger
– John Bollinger, Commented Jun 29, 2016 at 16:24
@JohnBollinger I dont like the 2 times I am using eligibility table in the where clause — srh
– srh, Commented Jun 29, 2016 at 16:30

Gordon Linoff · Accepted Answer · 2016-06-29 16:21:47Z

3

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;

answered Jun 29, 2016 at 16:21

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IVNSTN · Accepted Answer · 2016-06-29 16:48:21Z

1

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

answered Jun 29, 2016 at 16:48

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related