CODEWITHSUNDEEP
mysqlmaxmin
wearedoom
wearedoom

Reputation: 3

select min date from max id from table

I'm new here but I have one little big problem.

I have this table (for example)

id | client| date
------------------------------
 7 | v1    | 2015-05-20
 6 | v1    | 2015-05-20
 5 | v1    | 2015-05-01
 4 | v1    | 2015-03-05
 3 | v2    | 2015-02-02
 2 | v3    | 2015-01-30
 1 | v1    | 2015-01-01

I need the first row from the last client, this way is client v1, but when I select the min date the result is v1 from id = 7, but i don't need that id.

I need the id 4, because is the first (older) id from the last client.

I need a query from that result.

I use this query:

$re=mysql_query('SELECT traffic.*, client.alias FROM traffic INNER JOIN  service ON traffic.service_id = service.service_id INNER JOIN client ON service.client_id= client.client_id WHERE vehicle="'.$_GET['s'].'" AND client.alias = "'.$Days1.'" GROUP BY client.alias ORDER BY traffic.date ASC LIMIT 1');

My tables are:

mysql> select traffic_id, service_id, date, vehicle from traffic limit 5;
+-------------+-------------+---------------------+----------------+
| traffic_id  | service_id  | date                | vehicle        | 
+-------------+-------------+---------------------+----------------+
|     1057967 |     1106746 | 2012-01-02 06:35:23 | 2816           |
|     1057968 |     1106747 | 2012-01-02 06:35:55 | 2817           |
|     1057969 |     1106748 | 2012-01-02 06:36:11 | 48789          |
|     1057970 |     1106749 | 2012-01-02 06:37:27 | 48888          |
|     1057971 |     1106750 | 2012-01-02 06:37:59 | 48887          |
+-------------+-------------+---------------------+----------------+
5 rows in set (0.00 sec)


mysql> select service_id, client_id from service limit 5;
+-------------+------------+
| service_id  | client_id  |
+-------------+------------+
|     1106746 |          1 |
|     1106747 |          2 |
|     1106748 |          2 |
|     1106749 |          3 |
|     1106750 |          4 |
+-------------+------------+
5 rows in set (0.02 sec)

mysql> select client_id, alias from client limit 5;
+------------+------------+
| client_id  | alias      |
+------------+------------+
|          1 | EXPRESS    |
|          2 | CONWAY     |
|          3 | ACC        |
|          4 | GO         |
|          5 | ACCEL      |
+------------+------------+
5 rows in set (0.02 sec)

Any suggestions?

Upvotes: 0

Views: 354

Answers (2)

Abhishekh Gupta
Abhishekh Gupta

Reputation: 6236

You can give this a try:

SELECT 
  cd1.id,
  cd1.client,
  cd1.`date`
FROM clientData AS cd1
  INNER JOIN ( 
    SELECT *
    FROM clientData
    ORDER BY id DESC
    LIMIT 1
  ) AS cd2 ON cd1.client = cd2.client AND cd1.id != cd2.id
ORDER BY cd1.`date`
LIMIT 1;

Here is the sqlfiddle.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

So, for instance...

SELECT x.*
  FROM my_table x
  JOIN 
     ( SELECT * FROM my_table ORDER BY id DESC LIMIT 1 ) y
    ON y.client = x.client 
   AND y.id > x.id
 ORDER 
    BY x.id DESC 
 LIMIT 1;

Upvotes: 0

Related Questions