ONLY display max value for each ID

[Hallel86]

I did a search and I came up short on finding what I need here. I am looking for a solution to display THE max value of each well. For example. I have an ID number, a well, well_no, d_date, production, and the four_day_peak (which will be the max value)

I want to display ONLY the greatest 4_day_peak value for each well. So in this case I would only have two wells displayed. I will be applying this example solution to hundreds of results with hundreds of different [ID]s if that makes a difference (which I assume it won't). I attached a quick example of the data I am dealing with.

 

[SOS]

SELECT ID, Max(four_day_peak) As [MaxOfFourDayPeak]
FROM YourTableNameHere
GROUP BY ID
ORDER BY ID

 

[CJ_London]

The query you want is:

SELECT DISTINCT 
ID, 
Well, 
Well_No, 
(SELECT Max(four_day_peak) FROM tblWells as tmp where ID=tblWells.ID) AS MaxPeak 
FROM tblWells

This doesn't show d_date or production fields - from you post it doesn't look like they are requried. You'll need to change tblWells to the name of your table

 

[Hallel86]

SELECT ID, Max(four_day_peak) As [MaxOfFourDayPeak]
FROM YourTableNameHere
GROUP BY ID
ORDER BY ID

This works at first, but when I add more fields like Well and Well_NO it complicates this I suppose and everything is duplicated again. How would I narrow this down with more fields?

Edit: Correction, when I add dates it duplicates everything. Well and Well_NO will not change it from the results I want.

 

[CJ_London]

If you want all the fields then use

 [I]SELECT * 
FROM tblWells[/I]
[I]WHERE four_day_peak=(SELECT Max(four_day_peak) FROM tblWells as tmp where ID=tblWells.ID)[/I]

 

[Hallel86]

Silly question, but do you know why this would take like 3 minutes to query now? I have other queries that narrow down the data so it shouldn't take that long. and that code worked! Thank you!


edit: nvm. I just made previous query into a table and it works fine now. Thanks.

 

[CJ_London]

make sure you have your table properly indexed - for this query you ideally need indexes on

four_day_peak
ID

 

[Brianwarnock]

If it continues to perform slowly try the stacked query approach.
Take the query posted by SOS and join it to the table on the two fields and select all the other fields required from the table. Run the new query.

Brian