• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

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
0
VBdotnet2005
Asked:
VBdotnet2005
1 Solution
 
ezraaCommented:
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now