SQL Select, Specific Rows based on multiple conditions?

Question

Not sure if it's because I'm tired, but I can't seem to figure this out...

I'm looking for a Query that will filter the data based on a couple items...

Sample Data:

Business_Month  ID  Calls   Transferred Loaded
11/1/2012 0:00  119118  226 16  12/19/12 15:56
12/1/2012 0:00  119118  333 17  1/15/13 23:54
1/1/2013 0:00   119118  284 6   3/13/13 17:49
1/1/2013 0:00   119118  284 6   3/20/13 13:03
1/1/2013 0:00   119118  284 6   3/20/13 13:25
2/1/2013 0:00   119118  219 8   3/20/13 13:25
3/1/2013 0:00   119118  17  0   3/20/13 13:03
3/1/2013 0:00   119118  17  0   3/20/13 13:25
11/1/2012 0:00  120327  216 13  12/19/12 15:56
12/1/2012 0:00  120327  211 12  1/15/13 23:54
1/1/2013 0:00   120327  255 8   3/13/13 17:49
1/1/2013 0:00   120327  255 8   3/20/13 13:03
1/1/2013 0:00   120327  255 8   3/20/13 13:25
2/1/2013 0:00   120327  166 9   3/20/13 13:25
3/1/2013 0:00   120327  13  2   3/20/13 13:03
3/1/2013 0:00   120327  13  2   3/20/13 13:25

What I would like returned, is one line, Per Business month, for each unique ID with the highest Loaded Date...

Sample Output:

Business Month  ID  Calls   Transferred Loaded
11/1/2012 0:00  119118  226 16  12/19/12 15:56
12/1/2012 0:00  119118  333 17  1/15/13 23:54
1/1/2013 0:00   119118  284 6   3/20/13 13:25
2/1/2013 0:00   119118  219 8   3/20/13 13:25
3/1/2013 0:00   119118  17  0   3/20/13 13:25
11/1/2012 0:00  120327  216 13  12/19/12 15:56
12/1/2012 0:00  120327  211 12  1/15/13 23:54
1/1/2013 0:00   120327  255 8   3/20/13 13:25
2/1/2013 0:00   120327  166 9   3/20/13 13:25
3/1/2013 0:00   120327  13  2   3/20/13 13:25

I've tried different things, but can't seem to figure it out... I'm using MS SQL Server...

Hopefully someone can help.

Thanks!

Can you share what you've tried?

Nunser
– Nunser

2013-04-16 02:06:45 +00:00
Commented Apr 16, 2013 at 2:06 — Nunser
– Nunser, Commented Apr 16, 2013 at 2:06

Aaron Bertrand · Accepted Answer · 2013-04-16 01:53:31Z

1

;WITH x AS 
(
  SELECT [Business_Month], ID, Calls, Transferred, Loaded,
    rn = ROW_NUMBER() OVER 
    (PARTITION BY ID, [Business Month] ORDER BY Loaded DESC)
  FROM dbo.yourtable
)
SELECT [Business Month], ID, Calls, Transferred, Loaded
FROM x
WHERE rn = 1
ORDER BY ID, [Business Month];

answered Apr 16, 2013 at 1:53

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

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

Comments

Taryn · Accepted Answer · 2013-04-16 02:00:39Z

1

You can use a subquery to get the max(loaded) value for each business_month and then join that back to yourtable to get the desired result:

select t1.Business_Month,
  t1.ID,
  t1.Calls,
  t1.Transferred,
  t1.Loaded
from yourtable t1
inner join
(
  select Business_Month, 
    max(Loaded) MaxLoaded
  from yourtable
  group by Business_Month
) t2
  on t1.Business_Month = t2.Business_Month
  and t1.Loaded = t2.MaxLoaded
order by t1.id, t1.business_month;

See SQL Fiddle with Demo

answered Apr 16, 2013 at 2:00

Taryn

249k57 gold badges374 silver badges409 bronze badges

Collectives™ on Stack Overflow

SQL Select, Specific Rows based on multiple conditions?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related