help on query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

help on query

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-12 : 03:53:18

i have a tablel ike this :
identity column,accout number,branch,bank,statusid
and there can be an account with all the same deteils apear more then once widht diffrent statuses!
for example :
(1),12345,12,3,1
(15000)12345,12,3,3
(60001),12345,12,3,4
(60333),12345,12,3,0
(61234),12345,12,3,3

and i want to make a query that will get me te last bank account (where last is by the heighest (identity column) where its statusid for example=3 but!!! it must be in the most top id!
if in this example i wanted status 0 of the above account i whouldnt get anything beacuse 0 is not in the most top(id)
how can i do this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-12 : 03:57:00

quote:
it must be in the most top id!

What do you mean by that? Could you explain a bit?

Also you haven't provided column names..

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 03:59:16

select t.*
from mytable t
inner join (select max(idcol) maid, account from mytable where status = 3 group by account) d
on d.maid = t.idcol and d.account = t.account

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-12 : 04:15:58

yes i did this are the coklumns names :
identity column,accout number,branch,bank,statusid

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-10-12 : 04:25:50

Peso,
I don't think that's quite what he's after. That's going to return a record even if there are more recent entries of different statuses. I think what he wants is something like this:


-- for account=12345 and status=3
select t.*
from mytable t
inner join (select max(idcol) maid, account from mytable group by account) d
on d.maid = t.idcol and d.account = t.account
where t.account=12345
and t.status=3

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 04:32:59

You are right.
Delete the WHERE t.Status = 3 in the derived table and add it after the INNER JOIN in my code.

Peter Larsson
Helsingborg, Sweden

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-10-12 : 04:41:20

i did so and it gave the corect result!
thnaks alot

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources