CODEWITHSUNDEEP
phpmysqlselect
Universal Grasp
Universal Grasp

Reputation: 1845

In MySql, SELECT all with DIFFERENT id?

I Have a Table like,

(Please note, this question might sound like duplicate but I checked other questions before posting this... If you find the answer in other posts, Advice me rather than downvoting. I promise to delete it just incase. thx)

|tid |name|age|sex|pack_id
 1   |Joe |57 |M  |77
 2   |Joe |57 |M  |77
 3   |Joe |57 |M  |89
 4   |Joe |57 |M  |89
 5   |Joe |57 |M  |94

 6   |Roy |98 |M  |7
 7   |Roy |98 |M  |8
 8   |Roy |98 |M  |9
 9   |Roy |98 |M  |7
 10  |Roy |98 |M  |7
 11  |Roy |98 |M  |7

 12  |Joe |46 |M  |96

 13  |Guh |97 |F  |1

 14  |Kin |68 |F  |33
 15  |Kin |68 |F  |37
 16  |Kin |68 |F  |37
 17  |Kin |68 |F  |37
 18  |Kin |68 |F  |35
 19  |Kin |68 |F  |37

 20  |Pit |43 |F  |77
 21  |Pit |43 |F  |1
 22  |Pit |43 |F  |2
 23  |Pit |43 |F  |1
 24  |Pit |43 |F  |77

 25  |Joe |71 |M  |75

 26  |Bob |82 |M  |77

 27  |Guh |97 |F  |20
 28  |Guh |97 |F  |28
 29  |Guh |97 |F  |28
 30  |Guh |97 |F  |23

My goal is to have a Query that will produce none duplicate pack_id Something like:

|tid |name|age|sex|pack_id
 1   |Joe |57 |M  |77    
 3   |Joe |57 |M  |89
 5   |Joe |57 |M  |94

 6   |Roy |98 |M  |7
 7   |Roy |98 |M  |8
 8   |Roy |98 |M  |9

 12  |Joe |46 |M  |96

 13  |Guh |97 |F  |1

 14  |Kin |68 |F  |33
 18  |Kin |68 |F  |35
 19  |Kin |68 |F  |37

 20  |Pit |43 |F  |77
 21  |Pit |43 |F  |1
 22  |Pit |43 |F  |2

 25  |Joe |71 |M  |75

 26  |Bob |82 |M  |77

 27  |Guh |97 |F  |20
 28  |Guh |97 |F  |28

 30  |Guh |97 |F  |23

I have tried:

SELECT `tid` ,`name`,`age`,`sex`,`pack_id` FROM `myTable` group by `pack_id` //FAILED

Tried this aswell:

SELECT DISTINCT(`pack_id`) ,`name`,`age`,`sex`,`pack_id` FROM `myTable` group by `pack_id`// RETURNS ONE SINGLE 'name' MANY TIMES

Tried

MySQL select distinct where in all

Tried

How can i get all the unique id having more than one id with different address

Tried many more...

Still can't do it.

Upvotes: 1

Views: 119

Answers (2)

Randy
Randy

Reputation: 16677

SELECT min(tid), name,age,sex,pack_id 
FROM myTable 
group by name, age, sex, pack_id

Upvotes: 1

vhu
vhu

Reputation: 12788

It sounds like you need to GROUP BY on two levels: name and pack_id. For example:

SELECT tid,name,age,sex,pack_id FROM yourtable GROUP BY name, pack_id;

See reference manual for more deatils

Upvotes: 1

Related Questions