1

Hiho, i got following table:

+----+----+-------+----------+
| p1 | p2 | value | position |
+----+----+-------+----------+
|  1 |  5 |     0 |        1 |
|  1 |  6 |     0 |        2 |
|  1 |  7 |     0 |        3 |
|  1 |  7 |     1 |        4 |
|  1 |  8 |     1 |        5 |
+----+----+-------+----------+

as result of a query in need exactly this result:

+----+----+-------+----------+
| p1 | p2 | value | position |
+----+----+-------+----------+
|  1 |  5 |     0 |        1 |
|  1 |  6 |     0 |        2 |
|  1 |  7 |     1 |        4 |
|  1 |  8 |     1 |        5 |
+----+----+-------+----------+

As you will notice the dateset with the same p1 and p2 combination must be grouped, but i need the dataset with the highest position value. This behavior must be guaranteed.

I have tried something like:

SELECT *
FROM table
GROUP BY p1, p2
HAVING MAX(position)

without a proper result. Does anyone have an idea?

jcl
  • 11
  • 1

3 Answers3

1

http://sqlfiddle.com/#!9/499b3/3

SELECT t.*
FROM `table` t
LEFT JOIN `table` t1
ON t.p1 = t1.p1
  AND t.p2 = t1.p2
  AND t.position<t1.position
WHERE t1.p1 IS NULL
Alex
  • 16,739
  • 1
  • 28
  • 51
0

There are several ways of doing this. Here's one option using aggregation in a subquery:

select t.*
from yourtable t
   join (select p1, p2, max(position) maxposition
         from yourtable
         group by p1, p2) t2 on t.p1 = t2.p1 
                            and t.p2 = t2.p2
                            and t.position = t2.maxposition
sgeddes
  • 62,311
  • 6
  • 61
  • 83
  • problem might be on the `explain` of it as compared to Alex's. Depending on actual schema. – Drew Oct 27 '15 at 18:52
  • @Drew -- yeah, there are a lot of different ways to do this. Other databases make it a lot easier in my opinion. Alex's revised answer will definitely work as well (and should be more efficient than `not exists`). – sgeddes Oct 27 '15 at 19:00
0
SELECT RESULT.* 
         FROM resultset RESULT 
              INNER JOIN 
              (SELECT p1,p2,value,(MAX(position)) AS position
               FROM resultset GROUP BY p1, p2) AS T2
              ON RESULT.p1=T2.p1 AND RESULT.p2= T2.p2 AND RESULT.position=T2.position

This will work fine

Subin Chalil
  • 3,531
  • 2
  • 24
  • 38