I have NO idea how to write a query with MAX(). help? 1

[JeremyG]

I'm trying to write a query that returns the following:

1. Field #1 from TableA
2. Field #2 from TableB (only when it's SoftwareID matches the ID in TableA, there are multiple records in TableB that match TableA)
3. Return ONLY the record which has the highest value of Field #3 in TableB

This is what i tried, but it doesnt work like I want it to:

----------------
SELECT s.SoftwareName, s.Status, MAX(p.PackageRev), p.status
From swProducts s, swPackages p
Where s.softwareID = p.softwareID
----------------

PLEASE! this is driving me crazy

 

[tlbroadbent]

Does this do what you want?

SELECT
s.SoftwareName,
s.Status,
p.PackageRev,
p.status
From swProducts s
Inner Join swPackages p
On s.softwareID = p.softwareID
Where p.PackageRev In
(Select Max(PackageRev)
From swPackages
Where SoftwareID=p.SoftwareID) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[JeremyG]

Yes! exactly what I need, thanks alot. I was actually getting close to it, was trying the "EXISTS" function, but you saved me alot of time!!!