mysql distinct 失效,为什么我的MySQL DISTINCT不起作用？

Why the two query below return duplicate member_id and not the third?

I need the second query to work with distinct. Anytime i run a GROUP BY, this query is incredibly slow and the resultset doesn’t return the same value as distinct (the value is wrong).

SELECT member_id, id

FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u

LIMIT 5

+———–+——–+

| member_id | id |

+———–+——–+

| 11333 | 313095 |

| 141831 | 313094 |

| 141831 | 313093 |

| 12013 | 313092 |

| 60821 | 313091 |

+———–+——–+

SELECT distinct member_id, id

FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u

LIMIT 5

+———–+——–+

| member_id | id |

+———–+——–+

| 11333 | 313095 |

| 141831 | 313094 |

| 141831 | 313093 |

| 12013 | 313092 |

| 60821 | 313091 |

+———–+——–+

SELECT distinct member_id

FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u

LIMIT 5

+———–+

| member_id |

+———–+

| 11333 |

| 141831 |

| 12013 |

| 60821 |

| 64980 |

+———–+

my table sample

CREATE TABLE `table1` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`member_id` int(11) NOT NULL,

`s_type_id` int(11) NOT NULL,

`created_at` datetime DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `s_FI_1` (`member_id`),

KEY `s_FI_2` (`s_type_id`)

) ENGINE=InnoDB AUTO_INCREMENT=313096 DEFAULT CHARSET=utf8;

解决方案

it works, its dirty (no index, no key, temporary table…) but it works,

SELECT member_id,id

FROM ( SELECT member_id,id, created_at FROM table1 ORDER BY created_at desc ) as u

group by member_id ORDER BY created_at desc LIMIT 5;