2

I have this table:

id|date      |x |y
1 |2017-01-01|1 |0
2 |2017-01-01|23|1
4 |2017-01-02|1 |0

I would select the columns x and date, where the grouped row by date have the highest value of y.

In this case i would:

id|date      |x |
2 |2017-01-01|23|
4 |2017-01-02|1 |

This table is derived from another join:

X table:

date
2017-01-01
2017-01-02
....

Y table:

from      |to        |x |y 
2017-01-01|2017-01-10|1 |0
2017-01-01|2017-01-05|23|1

X join with Y on date between from and to.

How can I do? Thanks in advance.

sirion1987
  • 111
  • 4
  • 13
  • FWIW, I'd go with the faster, more verbose option. – Strawberry Jan 16 '17 at 14:50
  • Possible duplicate of [SQL Select only rows with Max Value on a Column](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – fafl Jan 16 '17 at 14:51

3 Answers3

3

In a subquery, find the max(y) for each date, then join to this.

select b.*
from MyTable b
inner join
  (
    select a3.date, max(a3.y) as maxy
    from MyTable a3
    group by a3.date
  ) a
  on a.date = b.date
  and b.y = a.maxy

Demo here:

SQL Fiddle

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
JohnHC
  • 10,935
  • 1
  • 24
  • 40
2

An alternative to the answer given by @JohnHC would be to use a correlated subquery:

SELECT t1.*
FROM yourTable t1
WHERE t1.y = (SELECT MAX(y) FROM yourTable t2 WHERE t2.date = t1.date)

While I would expect this to run more slowly than the join option, at least for larger datasets, this does have the option of being less verbose.

Demo here:

SQL Fiddle

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Join the table with itself, where the dates are equal and so that the second table has a bigger x than the first. Then filter so that only results remain, where the second x is NULL, meaning that the first x is the max.

SELECT a.*
FROM myTable a
LEFT OUTER JOIN myTable b
    ON a.date = b.date AND a.x < b.x
WHERE b.x IS NULL;
fafl
  • 7,222
  • 3
  • 27
  • 50
  • Note that this solution, while valid, scales poorly and will perform badly on larger data sets - but it's called a 'strawberry' query, so I have a natural fondness for it. – Strawberry Jan 16 '17 at 14:59
  • True, the answer from JohnHC should be the fastest, because the inner query is independent from the outer query – fafl Jan 16 '17 at 15:04
  • My table is derived from another join table. Must I use a double-join on the same table? – sirion1987 Jan 16 '17 at 15:16
  • Where do the IDs come from? They are not in any table – fafl Jan 16 '17 at 19:00