1

Im trying to select the entire row that has the heighest value in points. When Im trying to GROUP it turns out that I cant select entire row. So I came up with this query down here. 

SELECT i1.*
FROM objects i1
LEFT OUTER JOIN objects i2
  ON (i1.product = i2.product AND i1.points < i2.points)
WHERE i2.product IS NULL and i1.product < $day
order by product asc, points desc

The table:

product   name      points   message
----------------------------------------
1         Joe       4        Message 1
2         Steve     15       Message 2
2         Loui      17       Message 3
3         Larry     10       Message 4
3         Harry     10       Message 5
3         Hank      8        Message 6

I want result like this:

product   name      points   message
----------------------------------------
1         Joe       4        Message 1
2         Loui      17       Message 3
3         Larry     10       Message 4

Now, product will be returned twice if two rows have the same points.

How can I select so I only got one row for each product with the highest points?

LetsGoRangers
  • 640
  • 1
  • 7
  • 18

2 Answers2

0

how about: 

SELECT i1.*
FROM objects i1
where points= (select max(points) from objects i2 where i1.product=i2.product)

and sql fiddle: http://sqlfiddle.com/#!2/4caa0/1

Kiril Rusev
  • 745
  • 3
  • 9
  • Nested query like this one is more straightforward and more intuitive but less effective, I think. – peter.petrov Dec 02 '13 at 13:30
  • I completely agree with you, however I am not quite advanced in MySQL. If the question was MS SQL Server, I would try a different approach such as windowed function: `ROW_NUMBER over (partition by product order by points DESC) as seq` and then specify `seq=1` – Kiril Rusev Dec 02 '13 at 13:35
  • This also returns several rows with the same product id – LetsGoRangers Dec 02 '13 at 20:42
0

UPDATED

With MySQL variables, you can accomplish what you want. (but, not sure this is good way in practice) you can test here. http://www.sqlfiddle.com/#!2/d7fe7/4/0

SET @prev := 0; -- Initialize

SELECT *
FROM
(
    SELECT IF(@prev = o.product, 0, 1) just_one, @prev:=o.product,
        o.product, o.name, o.message, x.m_p
    FROM (
        SELECT product, MAX(points) m_p
        FROM objects
        GROUP BY product
    ) x INNER JOIN objects o ON x.product = o.product AND x.m_p = o.points
    ORDER BY o.product
) z
WHERE
    just_one = 1;

Assuming UNIQUE(product, name).

Orig. Answer

could you try this?

SELECT o.product, o.name, o.message, x.m_p
FROM (
    SELECT product, MAX(points) m_p
    FROM objects
    GROUP BY product
) x INNER JOIN objects o ON x.product = o.product AND x.m_p = o.points
Community
  • 1
  • 1
Jason Heo
  • 9,956
  • 2
  • 36
  • 64