1

The following query,

select shelf_id, issue_date, current_qty
from Stock
where barcode = '555' and issue_date <= '2018-05-30 14:28:32'

will give the following results,

10  2018-05-25 00:00:00 5
10  2018-05-28 00:00:00 55
5   2018-05-29 00:00:00 100

Adding group by shelf_id will lead to that result,

10  2018-05-25 00:00:00 5
5   2018-05-29 00:00:00 100

The desired result is the following.

10  2018-05-28 00:00:00 55
5   2018-05-29 00:00:00 100

The reasoning behind this, is that for each group I would like to return the row of the group with the latest issue_date.

limit 1 limits the total groups returned to just one, having issue_date... would be a possible solution but I do not know how to get the closest date to Max(issue_date)

Is it possible at all to accomplish this without using a subquery?

Edit:
The second condition in the where clause issue_date <= '2018-05-30 14:28:32' is a user input issue_date <= ?2 ment to initially filter the table, the query then should group by the results per shelf_if, but return the row with the closest day to the max(issue_date). So I don't see how I could just replace this condition with a subquery.

Christos Karapapas
  • 1,018
  • 3
  • 19
  • 40
  • Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) – Paul Spiegel May 29 '18 at 17:24

3 Answers3

0

Don't use group by! You are trying to filter the rows. Here is one method:

select s.*
from stock s
where s.issue_date = (select max(s2.issue_date) from stock s2 where s2.shelf_id = s.shelf_id);

As a bonus, with an index on stock(shelf_id, issue_date), the performance should be better than the group by.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Table aliases are really giving me a hard time in MySQL due to multiple joins, the query in my question is a simpler version of the real one. I am trying to implement your answer but so far I am only getting results consisting of one row. – Christos Karapapas May 29 '18 at 17:56
  • @Karapapas . . . You need the correlation clause to get more than one result. – Gordon Linoff May 30 '18 at 02:30
0

If you have identify column then you can use LIMIT clause: 

select s.*
from Stock s
where barcode = '555' and issue_date <= '2018-05-30 14:28:32' and 
      identity_col = (select identity_col
                      from Stock s1
                      where s1.shelf_id = s.shelf_id
                      order by s1.issue_date desc
                      limit 1
                     );
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
0

you may try it this way

select ST.shelf_id, ST.issue_date, ST.current_qty
from Stock as ST INNER JOIN (select shelf_id, MAX(issue_date) AS issue_date
from Stock
where barcode = '555' and issue_date <= '2018-05-30 14:28:32'
GROUP BY shelf_id) AS A ON ST.shelf_id = A.shelf_id and ST.issue_date = A.issue_date

as long as (shelf_id,issue_date) are unique, this should work, please let me know if im wrong

Kevin Villasana
  • 1
  • 3