how to select only max(position) from database where the memberid must be unique

Question

MY Table is

id  position  some data   
---|--------|------------|
1  | 1      | data       | 
1  | 2      | data       | 
2  | 1      | data       | 
2  | 2      | data       | 
5  | 1      | data       | 
5  | 2      | data       |
5  | 3      | data       |

how do i format it into the following. so that it shows only the unique ID with the highest position as following. The data is a longitude and latitude, so it should only return the highest position long and lat.

id  position  some data   
---|--------|------------|
1  | 2      | data       | 
2  | 2      | data       | 
5  | 3      | data       |

What do you mean by "format?" Are you trying to write a `SELECT`? What have you tried so far? Show some effort. — Matt Ball, Nov 17 '11 at 16:27
possible duplicate of [select top 10 records for each category](http://stackoverflow.com/questions/176964/select-top-10-records-for-each-category) — Clockwork-Muse, Jun 20 '14 at 04:00

score 1 · Accepted Answer · answered Nov 17 '11 at 16:35

1

You can join on a maximum-grouped set;

select *
from the_table inner join (
    select id, max(position) as max_pos
    from the_table
    group by id
    ) as T on the_table.id = T.id and the_table.position = T.max_pos
 order by the_table.id

answered Nov 17 '11 at 16:35

Alex K.

171,639
30
264
288

Thanks, I'll have to remember this (although still somewhat slow, this _is_ faster than the OLAP version). – Clockwork-Muse Nov 17 '11 at 17:13

score -1 · Answer 2 · answered Nov 17 '11 at 16:31

Supposing that data responds to an aggregate appropriately you can do this:

SELECT id, MAX(position), MAX(some_data)
FROM My_Table
GROUP BY id

If not, I believe that version has the necessary OLAP functions:

WITH Max_Data as (SELECT id, position, data, 
                         ROW_NUMBER() OVER(PARTITION BY id 
                                           ORDER BY position DESC) as rowNum
                  FROM My_Table)
SELECT id, position, data
FROM Max_Data
WHERE rowNum = 1

You'll want an index that includes position for best performance.

Please note that the use of the OLAP function may/will make it slower than @Alex's version — Clockwork-Muse, Nov 17 '11 at 17:12

score -2 · Answer 3 · answered Nov 17 '11 at 16:32

SELECT id, MAX(position), data
FROM yourtable
GROUP BY id

assming that the id field is your memberid. This'll fetch the largest position for each id. However, data is another matter. Do you need the actual data that's in the same record as the MAX(position), or just any data?

If you need it to be the data from the same record as the max id, then

SELECT yourtable.id, yourtable.position, yourtable.data
FROM yourtable
LEFT JOIN (
   SELECT id, MAX(position) AS position
   FROM yourtable AS child
   GROUP BY id
) AS child ON (yourtable.id = child.id AND yourtable.position = child.position

how to select only max(position) from database where the memberid must be unique

3 Answers3