-1

So I have one table

Table_A
ID    Code       Arrival date
3      A3           3/1/15
2      A2           1/1/15
1      A3           2/1/15

ID is auto increment, arrival date can be the same of the same code,

So what I want my MySQL query to do is to group all those codes with arrival date fits a range of dates and group them by code then choose only the latest input

What I have so far

Select * from Table_A
Where 'Arrival date' between 1/1/15 and 3/1/15 and group by Code

But somehow this only return the earliest code, how I can solve this?

Cheong D Mun Pong
  • 247
  • 1
  • 2
  • 10
  • Is there a particular reason why you are using 1/1/15 instead of the default `DATE` format (YYYY-mm-dd)? – Alejandro Iván Feb 04 '15 at 03:16
  • Not really just cause I'm typing with my phone, sorry – Cheong D Mun Pong Feb 04 '15 at 03:18
  • Does this work? `SELECT * FROM Table_A WHERE STR_TO_DATE('Arrival date', '%d/%m/%Y') BETWEEN '2015-01-01' AND '2015-01-03' GROUP BY Code asc ORDER BY 'Arrival date' DESC` – Alejandro Iván Feb 04 '15 at 03:21
  • No, the problem is which the grouping, only focus on the grouping part, the rest is fine, I want to achieve like I group those satisfied inputs with same code, but I just want the earliest inputs within those groups, which is with the biggest id number – Cheong D Mun Pong Feb 04 '15 at 03:24
  • I don't think so it will work, syntax error – Cheong D Mun Pong Feb 04 '15 at 03:26
  • What about this? `SELECT id, Code, MAX(\`Arrival date\`) AS \`Arrival date\` FROM Table_A WHERE \`Arrival date\` BETWEEN '2015-01-01' AND '2015-01-03' GROUP BY Code` – Alejandro Iván Feb 04 '15 at 03:41
  • No it won't work as if the codes are having same arrival date it will be duplicated? I want specifically the id should be biggest of number in each group only will be selected – Cheong D Mun Pong Feb 04 '15 at 03:43

2 Answers2

0

Give this a try. Seems more efficient to run multiple queries with your conditions then UNION them.

(SELECT * FROM Table_A WHERE 'Arrival date' 
 BETWEEN 1/1/15 AND 3/1/15 AND Code = A2 
 ORDER BY 'Arrival date' DESC LIMIT 1)
UNION ALL
(SELECT * FROM Table_A WHERE 'Arrival date' 
 BETWEEN 1/1/15 AND 3/1/15 AND Code = A3 
 ORDER BY 'Arrival date' DESC LIMIT 1)

Ordering by date allows us to sort the rows from newest to oldest (DESC) and return only the newest row. Hopefully it's what you need.

EternalHour
  • 8,308
  • 6
  • 38
  • 57
0

I believe this could help you:

SELECT
    MAX(id) AS id,
    Code,
    `Arrival date`
FROM
    Table_A
WHERE
    `Arrival date`
        BETWEEN
            '2015-01-01'
            AND
            '2015-01-03'
GROUP BY
    Code
Alejandro Iván
  • 3,969
  • 1
  • 21
  • 30
  • I see this but I don't know how to implement in my system http://stackoverflow.com/questions/9192673/returning-the-last-row-of-each-group-by-in-mysql – Cheong D Mun Pong Feb 04 '15 at 03:47