CODEWITHSUNDEEP
mysqlsqlgroup-by
ericlee
ericlee

Reputation: 2753

Mysql remove duplicate using group by?

Hi Guys I currently have this table

MEMBER

**ID Position Latitude Longitute**

1   1       1.38086  103.749 
1   2       1.38086  103.749 
1   3       1.38086  103.749    
1   4       1.48086  103.949 
1   5       1.48086  103.949    
1   6       1.48086  103.949 
1   7       1.58086  103.749

I am current using select * from meber group by latitude,longitute order by position desc.

however this will result in 

**ID Position Latitude Longitute**
1   1       1.38086  103.749    
1   4       1.48086  103.949  
1   7       1.58086  103.749

i wanted the result showing the highest position instead of the lowest position using the group by. any solution

Upvotes: 1

Views: 139

Answers (3)

Olivier Coilland
Olivier Coilland

Reputation: 3096

The subquery solution is the most common one.

There's another I like to use as it doesn't involve the subquery:

SELECT *
FROM member M
LEFT JOIN member N
    ON M.latitude = N.latitude
    AND M.longitute = N.longitute
    AND M.position < N.position
WHERE N.position IS NULL

Note that in order to get the lowest position it's as simple as writing M.position > N.position instead.

Upvotes: 0

eggyal
eggyal

Reputation: 125865

You have to use a subquery to determine the maximal position for each group, then join that with your select to obtain the desired records:

SELECT *
FROM   meber NATURAL JOIN (
  SELECT   latitude,longitute,MAX(position) AS position
  FROM     meber
  GROUP BY latitude,longitute
) AS t

Incidentally, the English word is spelled "longitude".

Upvotes: 4

nosid
nosid

Reputation: 50044

select MAX(position) position,latitude,longitute
from meber
group by latitude,longitute

Upvotes: 2

Related Questions