How to get just one result when using MAX and GROUP BY

I would love some help on this query:

select *
from STORAGE_MOVEMENT 
where  object_id = 43500 and current_location != 6411 and object_class = 'SAMPLE'

Here’s what I get:

enter image description here

I’m trying to fetch only the result with the biggest move_number of these two. With a MAX(move_number) it would work, but I also need other information, specifically the data in the Moved_On column.

I’m trying to make it work with this query but it doesn’t work :

SELECT MAX(move_number), object_id, original_location, current_location, moved_on   
FROM    
    (SELECT *
     FROM STORAGE_MOVEMENT 
     WHERE object_id = 43500 AND current_location != 6411 AND object_class = 'SAMPLE'
     ) A
GROUP BY 
    object_id, original_location, current_location, moved_on

But this shows basically the same result as the previous query :

enter image description here

Thanks.

Answer

Looks like you want top(1)

 SELECT top(1) *
 FROM STORAGE_MOVEMENT 
 WHERE object_id = 43500 AND current_location != 6411 AND object_class = 'SAMPLE'
 ORDER BY move_number DESC

Advertisement

Answer