Pulling the highest value from duplicates

[ciarra41]

I am trying to select the highest value from a duplicate set of rows by each column.


Field1 Field2 Field3 Field4 Field5
Sara 2454 Town Lake 487
Sara 2454 Town Lake 500
Sara 2454 Town River 87


It needs to pull the highest value from duplicates when there are duplicate rows. By this illustration it will select row two. Kindly help, please!


Thanks in advance

 

[Remou]

How about:

SELECT Field1, Field2, Field3, Max(Field5) AS MaxOfField5
FROM tblTable
GROUP BY Field1, Field2, Field3

 

[ciarra41]

Row three is not null values and that's when I see the problem I have. I've tried your suggestion before but I don't see the duplicates in Field1 through Field3. I think I need to find the duplicates first and then see from Field5 which is the highest.

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmm ... thinking ... mmmmmmmmmmmmmmmmmmm ...

but if fields where n= 1,2,3 are the same (which they are by the deffiinition of the grouping clause) then you would not see any difference in the seeing of the other records, so the mmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmm ... thinkiing ... is wondering what I don't understand about the question .................... mmmmmmmmmmmmmmmmmm ...




MichaelRed