Different Results for Same SELECT Query When Executing on phpmyadmin and SQL Fiddle

Question

I wrote a query to fetch latest tax of all countries in a MySQL Table. Same Query Executed in phpmyadmin(localhost) and SQL Fiddle. Both results are different. But SQL Fiddle Producing the Correct Result.

I Could able to find same question Posted on Stack Overflow Earlier at this Link

I didn't get any help from the post. My Issue is how get the correct result on phpmyadmin(localhost) for the SELECT Query I wrote below; is there any problem with my SELECT Query?

Select Query: (Which I Executed on Both Phpmyadmin and SQL Fiddle)

SELECT 
  t1.id, t1.country, 
  t1.tax, t1.created_by, 
  t1.created_on, t1.modified_by, t1.modified_on 
FROM tax t1 
INNER JOIN 
(
   SELECT country, MAX(created_on) AS latest 
   FROM tax GROUP BY country
) t4 ON t1.created_on=t4.latest AND t1.country=t4.country;

Table

+-------------+--------------+------+-----+-------------------+-----------------
------------+

| Field       | Type         | Null | Key | Default           | Extra
            |

+-------------+--------------+------+-----+-------------------+-----------------
------------+

| id          | int(11)      | NO   | PRI | NULL              | auto_increment
            |

| country     | int(11)      | NO   |     | NULL              |
            |

| tax         | decimal(8,5) | NO   |     | NULL              |
            |

| created_by  | int(11)      | NO   |     | NULL              |
            |

| created_on  | timestamp    | NO   |     | CURRENT_TIMESTAMP |
            |

| modified_by | int(11)      | YES  |     | NULL              |
            |

| modified_on | timestamp    | YES  |     | NULL              | on update CURREN
T_TIMESTAMP |

+-------------+--------------+------+-----+-------------------+-----------------
------------+

Create Table Query: (Which I tried in SQL Fiddle)

CREATE TABLE tax ( id int(11) NOT NULL AUTO_INCREMENT, country int(11) NOT NULL, tax decimal(8,5) NOT NULL, created_by int(11) NOT NULL, created_on timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, modified_by int(11) DEFAULT NULL, modified_on timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) );

INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,7,51,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,7,51,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,7,49,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,7,50,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,7,48,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (6,9.45450,51,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (3,8.88900,49,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (2,9.08989,49,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (2,4.00087,49,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,1.88900,49,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (4,5.54656,51,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (5,7.45435,50,'2015-06-26 16:26:20');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,43.88776,46,'2015-06-26 17:30:18');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (2,5.67,46,'2015-06-26 17:39:12');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (12,0.009,46,'2015-06-26 17:48:35');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (11,3,46,'2015-06-26 17:55:14');
INSERT INTO tax(country, tax, created_by, created_on) VALUES (1,5,46,'2015-06-26 17:55:39');

Please find SQL Fiddle Result @

Result Produced in phpmyadmin(localhost)

+----+---------+---------+------------+---------------------+-------------+-----
--------+

| id | country | tax     | created_by | created_on          | modified_by | modi
fied_on |

+----+---------+---------+------------+---------------------+-------------+-----
--------+

|  3 |       3 | 7.00000 |         49 | 2015-06-26 16:26:20 |        NULL | NULL
        |

|  5 |       5 | 7.00000 |         48 | 2015-06-26 16:26:20 |        NULL | NULL
        |

|  6 |       6 | 9.45450 |         51 | 2015-06-26 16:26:20 |        NULL | NULL
        |

|  7 |       3 | 8.88900 |         49 | 2015-06-26 16:26:20 |        NULL | NULL
        |

| 11 |       4 | 5.54656 |         51 | 2015-06-26 16:26:20 |        NULL | NULL
        |

| 12 |       5 | 7.45435 |         50 | 2015-06-26 16:26:20 |        NULL | NULL
        |

| 14 |       2 | 5.67000 |         46 | 2015-06-26 17:39:12 |        NULL | NULL
        |

| 15 |      12 | 0.00900 |         46 | 2015-06-26 17:48:35 |        NULL | NULL
        |

| 16 |      11 | 3.00000 |         46 | 2015-06-26 17:55:14 |        NULL | NULL
        |

| 17 |       1 | 5.00000 |         46 | 2015-06-26 17:55:39 |        NULL | NULL
        |

+----+---------+---------+------------+---------------------+-------------+-----
--------+

The obvious reason that you get different results is that you use different source data. In the example output for phpmyadmin there are two rows not present in the dataset used with sql fiddle. — jpw, Jun 29 '15 at 02:09
Oh My Bad. You are right? Now I'm getting both Results same @jpw — Syed Nizamudeen, Jun 29 '15 at 02:13
Can help me to write Query to get all latest `tax` of each `country` without duplication? — Syed Nizamudeen, Jun 29 '15 at 02:17
@jpw It is but. if you see `country` column in `Result Produced in phpmyadmin(localhost)` Table Id 3 and 5 is duplicated. I want only the latest `tax` of each `country`. — Syed Nizamudeen, Jun 29 '15 at 02:22
@jpw you can refer this link also [link](http://sqlfiddle.com/#!9/ee2c1/1) — Syed Nizamudeen, Jun 29 '15 at 02:25
The reason they're duplicated is that the rows has the same dates, how can you tell which is the latest? Maybe you can use the id attribute and do another join: http://sqlfiddle.com/#!9/ee2c1/2 — jpw, Jun 29 '15 at 02:26
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/81822/discussion-between-jpw-and-syed-nizamudeen). — jpw, Jun 29 '15 at 02:41

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

Check out this link to see what the error log is saying.

This is very wrong because those results dont even make sense. where can I find mysql logs in phpmyadmin?

Also see if simple select statements are working as they should.

edited May 23 '17 at 12:06

Community

1
1

answered Jun 29 '15 at 02:02

ABrowBoyGenius

73
7

Different Results for Same SELECT Query When Executing on phpmyadmin and SQL Fiddle

1 Answers1