CODEWITHSUNDEEP
phpsqlmysql
belaz
belaz

Reputation: 1506

Why my mysql DISTINCT doesn't work?

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;

Upvotes: 3

Views: 6249

Answers (5)

Quassnoi
Quassnoi

Reputation: 425813

Create the following indexes:

CREATE INDEX ix_table1_createdat ON table1 (created_at);
CREATE INDEX ix_table1_memberid_createdat ON table1 (member_id, created_at);

and use this query:

SELECT  t1i.*
FROM    (
        SELECT  DISTINCT member_id
        FROM    table1 tdi
        ORDER BY
                created_at DESC
        LIMIT 5
        ) t1d
JOIN    table1 t1i
ON      t1i.id =
        (
        SELECT  t1o.id
        FROM    table1 t1o
        WHERE   t1o.member_id = t1d.member_id
        ORDER BY
                t1o.member_id DESC, t1o.created_at DESC
        LIMIT 1
        )

Upvotes: 0

Ropstah
Ropstah

Reputation: 17804

SELECT distinct member_id, id FROM ( SELECT * FROM table1 ORDER BY created_at desc ) as u LIMIT 5

member_id is not unique. So that makes the query show more rows...

Upvotes: 1

belaz
belaz

Reputation: 1506

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;

Upvotes: 2

zneak
zneak

Reputation: 138251

DISTINCT is a keyword you can only apply on the whole SELECT, and not on a single field. It ensures the database doesn't return two identical rows. This is why your second query with DISTINCT returns only one time each member_id while your first returns it twice. In its result set, each row is indeed unique, even though you can get several times the same member_id.

Upvotes: 18

Midhat
Midhat

Reputation: 17840

In first query, there is no distinct keyword. In 2nd query it is selecting distinct rows. not distinct member id. In the third query, there is only member id, so it is selecting the distinct member ids

Upvotes: 1

Related Questions