Hi there,

I have a table bank_posn. The schema is given below:

BRANCH varchar(50),
DT varchar(50),
HDFC varchar(50) ,
AXIS varchar(50) ,
IDNT int

IDNT is the identity column.

Now suppose I have the following rows:

mumbai 2011-07-29 5000 3000 1
mumbai 2011-07-29 4000 3000 2
delhi 2011-07-29 5000 3000 3
delhi 2011-07-29 7000 3000 4

Now I want to display the following results:


mumbai 2011-07-29 4000 3000 2

delhi 2011-07-29 7000 3000 4

Please help me to sort out my problem. I have tried the following query but I'm clueless:
SELECT * FROM BANK_POSN
INNER JOIN (SELECT MAX(IDNT), BRANCH, DT, HDFC, AXIS, ICICI, BOB, PNB, FEDEBNK, IOB, CENTRALBNK, TOT FROM BANK_POSN GROUP BY BRANCH) T
ON
BP.BRANCH = T.BRANCH AND BP.IDNT = T.IDNT ORDER BY BP.IDNT