SQL Aggregate question

[kasavazala]

I am using max() function and want to know 2 values associated with the max. This is my query
BE
=====
Tnum BE_id BET
---------------------
1 1 45
1 2 3
1 3 6
1 4 54
1 5 1

I want to get Max(BE_id) and BET and Tnum associated with it.

Result
================
Tnum BE_id BET
---------------------
1 5 1

Can i have an SQL for this without having to join the table with itself.

Any help is greatly appreciated..

Thanks in advance

 

[lespaul]

You'll usually get better answers posting in the specific forum for the database you're working with rather than the General Database Discussion forum, but this should work for you:

SELECT TNum, MaxBE_ID, BET FROM TableName A
INNER JOIN (SELECT TNum, Max(BE_ID) As MaxBE_ID FROM TableName GROUP BY TNum) B ON A.TNum = B.Tnum AND A.BE_ID = B.MaxBE_ID


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[JarlH]

SELECT Tnum, BE_id, BET
FROM tablename
WHERE BE_id = (SELECT MAX(BE_id) FROM tablename)