Pulling MAX rows 1

[dheim70]

Hi, I am a Total SQL novice, but need to do this. I have a table as follows:

Contract_No ProgressID ReportingPeriod ReportingYear FinalFlag
2185 721 Jan-Dec 2010 Y
2185 831 Jun-Aug 2011 N
2190 895 jun-Sep 2011 Y
2190 840 Jun-Sep 2009 N

The rows I need to pull are the Max of progressID by Contract_No but full row. ie in the example above the query would pull rows 2 and 3 (All 5 fields). Any time I attempt any of this I get various error messages that all fields have to be done as aggregates and can't do grouping if fields are not all aggregates.I'm desparate and any help would be greatly appreciated! Thanks! D

 

[SQLScholar]

something like this

SELECT *
from
DataTable
INNER JOIN (

select contract_no, max(progressID) as maxProgID From DataTable) MaxList
on maxlist.contract_no = DataTable.contract_no and maxlist.maxProgID =DataTable.progressID

so its a self referencing query. So the Inner join queries the table to find the max for each entry. Then joins back to the main table to pull all of the data for that row.

Hope this helps

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------