CODEWITHSUNDEEP
mysqldistinct
Student
Student

Reputation: 1863

How to get distinct record from mysql table?

I have a table student like this

id | name | zip 
1  | abc  | 1234
2  | xyz  | 4321
3  | asd  | 1234

I want to get all records but zip code should not be repeated. So In case of above table records, record No 1 and 2 should be fetched. Record No. 3 will not be fetched because it has a zip code which is already in record No. 1

Upvotes: 16

Views: 56900

Answers (7)

symcbean
symcbean

Reputation: 48357

Since presumably the other columns are of some interest....

 SELECT y.*
 FROM yourTable y,
 (SELECT MIN(y2.id)
  FROM yourTable y2
  GROUP BY y2.zip) ilv
 WHERE ilv.id=y.id;

(or you could use the max-concat trick)

update

Oracle have now removed the max concat trick from the linked page - but it is described elsewhere on the internet

Upvotes: 2

sushil bharwani
sushil bharwani

Reputation: 30177

Is there any problem if I use as this below? 

select distinct zip,name,id from student;

Upvotes: 0

Deepesh
Deepesh

Reputation: 840

Try Using

Select Distinct(zip),id,name group by zip;

Upvotes: 0

user3464213
user3464213

Reputation: 1

select id, name, distinct(zip) from student;

Upvotes: -2

Mukesh Chapagain
Mukesh Chapagain

Reputation: 25948

SELECT DISTINCT fieldName FROM tableName;

The following query will only select distinct 'zip' field.

SELECT DISTINCT zip FROM student;

SELECT * FROM tableName GROUP BY fieldName;

The following query will select all fields along with distinct zip field.

SELECT * FROM student GROUP BY zip;

Upvotes: 41

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

Altough in MySQL you can get away with:

SELECT *
FROM student
GROUP BY zip

I would choose:

SELECT * 
FROM student t
  JOIN 
    ( SELECT MIN(id) AS minid
      FROM student
      GROUP BY zip
    ) AS grp
    ON grp.minid = t.id

Upvotes: 5

xkeshav
xkeshav

Reputation: 54016

TRY

 SELECT DISTINCT(zip),id,name FROM student;

OR 

  SELECT * FROM student GROUP BY zip;

Upvotes: 7

Related Questions