get latest by each client

I want to get the latest grouping by idx of each bank.
result like this
2011-02-11 11:08:32.700 BOFA   BANK OF AMERICA
2011-01-11 11:09:32.700 CHS     CHASE BANK
2011-02-12 11:09:32.700 AMC     AMERICAN HOME

Here is a sample table





MODIFIED DATE                IDX       BANK_NAME
2011-02-10 11:07:32.700 BOFA   BANK OF AMERICA
2011-02-11 11:08:32.700 BOFA   BANK OF AMERICA
2011-01-10 11:09:32.700 AMC     AMERICAN HOME
2011-01-10 11:09:32.700 CHS     CHASE BANK
2011-01-10 11:09:32.700 AMC     AMERICAN HOME
2011-02-11 11:09:32.700 BOFA   BANK OF AMERICA
2011-01-11 11:09:32.700 CHS     CHASE BANK
2011-02-12 11:09:32.700 AMC     AMERICAN HOME
VBdotnet2005Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ezraaConnect With a Mentor Commented:
You can select the max(date) and group by the IDX and BANK_NAME field:

select max([MODIFIED DATE]), IDX,  BANK_NAME
from MyTable
group by IDX,  BANK_NAME;


If you can have different BANK_NAMEs for a IDX you may need a query similar to this one to achive the output you're looking for:

select t.[MODIFIED DATE], t.IDX,  t.BANK_NAME
from (
   select IDX, max([MODIFIED DATE]) as [MODIFIED DATE]
   from myTable group by IDX
) as m inner join MyTable t on m.IDX = t.IDX and m.[MODIFIED DATE] = t.[MODIFIED DATE]

0
 
VBdotnet2005Author Commented:
thank you so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.