Select multiple rows with max date

Question

I'm new to mysql so please help me out with this.

I have a table containing the following columns:

nr  |   date   |  hour  |  user  |  shop  |  brand  |  categ  | product | price | promo
183   02/03/14   17:06    cristi    186     brand1    categ 1    prod 1    299     no
184   02/03/14   17:06    cristi    186     brand2    categ 2    prod 2    399     yes
184   01/03/14   17:06    cristi    186     brand3    categ 3    prod 3    199     no

The query that I use is

SELECT * 
FROM evaluari 
WHERE magazin = %s HAVING MAX(data)

Where "s" is the shop ID (186).

but that return only the first row containing 02/03/14 date. How can I show both/all rows containing the same max date?

Sari Alalem · Accepted Answer · 2014-03-02T16:00:45.220

3

Try not to name colums with reserved words like "date" it might cause you problems. You can do what you want like this:

SELECT * FROM evaluari WHERE magazin = 186 AND date = (SELECT MAX(date) from evaluari WHERE magazin = 186)

edited Mar 02 '14 at 16:00

answered Mar 02 '14 at 15:51

Sari Alalem

870
7
18

1

You want to put the magazin id to max date select or `SELECT MAX(date) from evaluari` could be an awful table scan... – Ray Mar 02 '14 at 15:53
@Ray: How to "put the magazin id to max date select"? – Sari Alalem Mar 02 '14 at 15:57
`(SELECT MAX(date) from evaluari Where magazin = 186)` – Ray Mar 02 '14 at 15:57
@SariAlalem also, without the magazin =186 the select, the max date might be tied to a magazin not being looked for, and you'd get zero results. – Ray Mar 02 '14 at 16:01
You might want to use the modifies answer, as its more optimied as Ray suggested :) – Sari Alalem Mar 02 '14 at 16:01
can I sort the rows by categ? How can I do that? – user3303717 Mar 02 '14 at 16:21
add to the end of the query "order by categ asc" or instead of asc, desc – Sari Alalem Mar 02 '14 at 16:42

score 1 · Answer 2 · answered Mar 02 '14 at 15:49

1

Probably, not optimal, but at first swing, you could do this

 SELECT * FROM evaluari 
   where date IN (SELECT date FROM evaluari WHERE magazin = %s HAVING MAX(date)) 
          AND magazin = %s;

In fact, this really rubs me as nasty... going to try to figure something smoother. Stay tuned :)

answered Mar 02 '14 at 15:49

Ray

40,256
21
101
138

1

The `HAVING` clause seems overkill in this query, why not just `SELECT MAX(date) WHERE magazin = %s` in the inner query? – André Laszlo Mar 02 '14 at 15:50
@AndréLaszlo yes, that's what I think, going to look for another better solution, but the above would work. – Ray Mar 02 '14 at 15:51

Select multiple rows with max date

2 Answers2