CODEWITHSUNDEEP
mysqlsql
cool_cs
cool_cs

Reputation: 1741

How to select distinct rows without using group by statement

A B C
1 1 1 
1 1 1 
2 2 2 
2 2 2 
3 3 3 
3 3 3 
4 4 4 
4 4 4 
5 5 5 
5 5 5 
5 5 5 
6 6 6
6 6 6

I am to output only the distinct rows without using the group by statement. I cannot use group by because it makes mysql hang. So it should return

1 1 1
2 2 2 
3 3 3 
4 4 4 
5 5 5 
6 6 6

I am using DISTINCT in for an inner join.This does not work either:

SELECT DISTINCT * FROM TABLEA inner join TABLEB on TABLEA.A = TABLEB.A

Upvotes: 17

Views: 49804

Answers (4)

dpelisek
dpelisek

Reputation: 1031

IF you (or anyone else) really wants to get distinct resutl without using DISTINCT or GROUP BY statement, this may be the way:

SELECT
    a.name
FROM person a
LEFT JOIN person b
    ON (a.name = b.name OR (a.name IS NULL AND b.name IS NULL))
    AND a.id < b.id
WHERE b.id IS NULL

It's rather a quaint thing, though.

Upvotes: 2

Jerome WAGNER
Jerome WAGNER

Reputation: 22412

SELECT DISTINCT A,B,C FROM TABLE;

According to mysql documentation, DISTINCT specifies removal of duplicate rows from the result set (http://dev.mysql.com/doc/refman/5.0/en/select.html)

I created a sample on jsfiddle and it works IMHO

create table tablea (A int,B int,C int);
create table tableb (A int);

INSERT INTO tablea VALUES (1,1,1),(1,1,1),(2,2,2),(2,2,2),(3,3,3),(3,3,3),(4,4,4),(4,4,4),(5,5,5),(5,5,5),(5,5,5),(6,6,6),(6,6,6);
INSERT INTO tableb VALUES (1),(1),(1),(1),(1);

SELECT DISTINCT tablea.A,tablea.B,tablea.C FROM tablea INNER JOIN tableb ON tablea.A=tableb.A;

feel free to experiment on this SQLFiddle.

Upvotes: 22

gexicide
gexicide

Reputation: 40048

the DISTINCT keyword does what you want. I.e., if the name of your table is A, select distinct * from A will do the trick.

Cf. mysql manual: http://dev.mysql.com/doc/refman/5.0/en/select.html

Upvotes: 2

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44343

This may totally shock you but MySQL uses GROUP BY under the hood to execute DISTINCT !!!

Here is something you may want to try

If the table is called mytable, do these two things:

First run this

ALTER TABLE mytable ADD INDEX ABC (A,B,C);`

Second, run this query

SELECT A,B,C FROM mytable GROUP BY A,B,C;

GROUP BY actually works better with an index present !!!

Here is sample code to prove it works

mysql> drop database if exists cool_cs;
Query OK, 1 row affected (0.04 sec)

mysql> create database cool_cs;
Query OK, 1 row affected (0.00 sec)

mysql> use cool_cs
Database changed
mysql> create table mytable
    -> (A int,B int,C int, key ABC (A,B,C));
Query OK, 0 rows affected (0.08 sec)

mysql> INSERt INTO mytable VALUES
    -> (1,1,1),(1,1,1),(2,2,2),(2,2,2),(3,3,3),
    -> (3,3,3),(4,4,4),(4,4,4),(5,5,5),(5,5,5),
    -> (5,5,5),(6,6,6),(6,6,6);
Query OK, 13 rows affected (0.06 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select a,b,c FROM mytable group by a,b,c;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |
|    3 |    3 |    3 |
|    4 |    4 |    4 |
|    5 |    5 |    5 |
|    6 |    6 |    6 |
+------+------+------+
6 rows in set (0.02 sec)

mysql>

Give it a Try !!!

Upvotes: 9

Related Questions