Sam
Reputation: 766
SQL Query using MAX() function in where condition and display data
I have a DataTable in which I have LCNo (string), AmmendmentNo (string). I want to retrieve the row which has highest AmmendmentNo for a respective LCNo
For example:
LCNO (123,123,123,123) AmmendmentNo (0,1,2,3)
I want the row which has LCNo: 123 and AmmendmentNo: 3
select * from LCTable where LCNo= '12147001' AND MAX(AmendmentNo);
Upvotes: 1
Views: 25268
Answers (2)
Gordon Linoff
Reputation: 1270713
For this, use limit or top or something similar:
select *
from LCTable
where LCNo= '12147001'
order by AmendmentNo desc
limit 1
In SQL Server, this would use top:
select top 1 *
from LCTable
where LCNo= '12147001'
order by AmendmentNo desc
Upvotes: 3
John Woo
Reputation: 263843
use GROUP BY clause. This will give you all LCNo with its highest AmendmentNo.
SELECT LCNo, MAX(AmendmentNo) AmendmentNo
FROM LCTable
GROUP BY LCNo
but if you want tfor a specific LCNo, you can simply use below. Remember that using the query below gets only one LCNo.
SELECT LCNo, MAX(AmendmentNo) AmendmentNo
FROM LCTable
WHERE LCNo = '12147001'
Upvotes: 2