CODEWITHSUNDEEP
mysqlsqlsql-server
Howard Hee
Howard Hee

Reputation: 929

SQL inner join same table to get max value

I have a table "tblTrasaction". I used the following code to get the max date:

select Max(t.TranDate) from tblTrasaction

Then I want use the max date value to get the max transaction ID,I used the following code:

   select Max(t.TranDate) from tblTrasaction t
   inner join
   (
      select Max(t.TranID) from tblTrasaction t
   ) temp On temp.TranID =t.TranID

But FAIL to get the result, how to I fix it? Thanks

Upvotes: 0

Views: 3388

Answers (3)

bendataclear
bendataclear

Reputation: 3850

Let me know if I've missed something but wouldn't it be easier to do:

If the original poster is using mysql:

select t.TranDate from tblTrasaction t
order by t.TranDate desc, t.TranID desc
limit 1

If the original poster is using SQL-Server:

select top 1 t.TranDate from tblTrasaction t
order by t.TranDate desc, t.TranID desc

Upvotes: 0

Robert
Robert

Reputation: 25753

Try this way:

select Max(t.TranID)
from tblTrasaction t
where t.TranDate in (select Max(t1.TranDate) from tblTrasaction t1)

Upvotes: 0

John Woo
John Woo

Reputation: 263693

Isn't it this way,

SELECT  MAX(TranID) TranID
FROM    tblTrasaction 
WHERE   TranDate = (SELECT MAX(TranDate) FROM tblTrasaction)

Upvotes: 1

Related Questions