how to select a distinct of two specific columns in sql query

Question

select distinct (pc.id,pc.PolicyPremiumID) ,pc.policyPremiumCatID,b.nin, b.firstname+' '+coalesce((b.middleInitial),'')+' '+b.lastname as fullname, b.gender,ppc.amount from bio_data b, medicalInsurance m, policy p, policyPremium pp,premiumCategories pc,policyPremiumCategory ppc where b.nin=m.patientBin and m.policyID=p.id and (p.id=pp.policyID and pp.policyID=p.id) and pp.id=pc.policyPremiumID and pc.policyPremiumcatID=ppc.id and p.id=82

in the above query, I want to have a distinct of those two columns it is duplicating instead of 7 return values it bring 14 Can some please help? Thanks in advance!!!

Murali · Accepted Answer · 2015-09-12 15:10:11Z

1

Use the following method:

Sample select query which list all values:

mysql> select * from new_table;
+---------+------------+
| premium | sumassured |
+---------+------------+
|    1000 |     100000 |
|    2000 |     200000 |
|    3000 |     300000 |
|    1000 |     100000 |
|    1000 |     100000 |
|    3000 |     300000 |
|    4000 |     400000 |
+---------+------------+
7 rows in set (0.00 sec)

Select Query which lists Distinct Value from multiple Tables:

mysql> select distinct premium,sumassured from new_table;
+---------+------------+
| premium | sumassured |
+---------+------------+
|    1000 |     100000 |
|    2000 |     200000 |
|    3000 |     300000 |
|    4000 |     400000 |
+---------+------------+
4 rows in set (0.00 sec)

You can use the same for N number of Rows.

edited Sep 12, 2015 at 15:10

answered Sep 12, 2015 at 15:04

Murali

1,1142 gold badges11 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lamin L. Janneh Over a year ago

thank murali!! but I want a situation where only the two will be considered where having other selection for example select distinct (premium,sumassured), something, something from new_table; the uniqueness of only this (premium,sumassured)

Murali Over a year ago

Sorry I am not getting what you mean, You need only two values or what, Give some clear explanation I will give you a Fix.

Lamin L. Janneh Over a year ago

sorry for that!!! ok!! I want only the premium column in the new_table to bring distinct values while other selection can bring any value for example select (distinct premium), sumassured from new_table this way only the premium will be affected by the distinct clause. thanks for the patient!!

Collectives™ on Stack Overflow

how to select a distinct of two specific columns in sql query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related