Max Field Value - 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

Transact-SQL (2000)

Max Field Value

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-27 : 21:15:06

From tblAidaLou

code - Name
AC - Aida
DD - Lou
EE - C

From table tblTransaction

TransID - code - amount
-----------------------------------------------
1 - AC - 1050
2 - AC - 2000
3 - DD - 11
4 - EE - 3123
5 - FF - 4444
6 - AC - 5456
7 - DD - 5345
8 - AC - 6656
9 - FF - 5435
10 - DD - 4214

I want to display the amount field of the maximum
TransID based on their code.

How?

ex Result...

TransID - code - amount
-----------------------------------------------
8 - AC - 6656
10 - DD - 4214
4 - EE - 3123

The shorter the querry the better...

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-27 : 21:33:05

select *
from tblTransaction t
where t.TransID = (select max(t2.TransID) from tblTransaction t2 where t2.code = t.code)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-27 : 22:12:23

Thnx NR your the saviour...

Want Philippines to become 1st World COuntry? Go for World War 3...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-27 : 22:17:31

But... i just want to display the record of it available on tblAidaLou...

Want Philippines to become 1st World COuntry? Go for World War 3...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-27 : 22:48:33

select t.transid,t.code,max(t.amount)
from tbltransaction t
join tblaidalou a
on t.code=a.code
group by t.transid,t.code

--------------------
keeping it simple...

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-10-28 : 02:00:52

tnx for the response jen but i don't want to get the maximum of amount.
Instead the amount of the maximum of TransID by code.

Want Philippines to become 1st World COuntry? Go for World War 3...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-28 : 02:05:30

the group by will give you that...

or did it?

--------------------
keeping it simple...

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2004-10-28 : 02:21:08

Does this give you what you want

SELECT TransId, T.Code, Amount
FROM tblTransaction T INNER JOIN tblAidaLou AL
ON T.Code = AL.Code
WHERE TransId IN (SELECT MAX(TransId) FROM tblTransaction GROUP BY Code)

Andy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources