In MySql, SELECT ALL with DIFFERENT ID?

advertisements

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.


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