How can I Select Distinct values with multiple columns and tables in SQL server?

Question

Here is my code:

SELECT registryno,registrystatus,psPatRegisters.pattrantype,PK_psPatRegisters,
       payername,*
FROM psPatRegisters,psPatinv
WHERE psPatRegisters.PK_psPatRegisters=psPatinv.FK_psPatRegisters
    AND registryno between '7133' and '7169'
    AND psPatinv.pattrantype='I'
GROUP BY psPatRegisters.PK_psPatRegisters

What's wrong with my code?

Perhaps you could give us a sample of the output you are expecting — Timothy Walters
– Timothy Walters, Commented Sep 30, 2013 at 6:00

Pradeeshnarayan · Accepted Answer · 2013-09-30 06:02:35Z

2

remove the "group by" and "*" and use distinct. Or you have to group by all the selected fields (without *) at that time distinct is not required

answered Sep 30, 2013 at 6:02

Pradeeshnarayan

1,23510 silver badges21 bronze badges

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

Comments

Szymon · Accepted Answer · 2013-09-30 05:59:26Z

0

Would that work for you?

select distinct egistryno,registrystatus,
       psPatRegisters.pattrantype,PK_psPatRegisters,payername
from psPatRegisters,psPatinv
where psPatRegisters.PK_psPatRegisters=psPatinv.FK_psPatRegisters
and registryno between '7133' and '7169'
and psPatinv.pattrantype='I'

Also, you cannot group and select columns that are not in the grouping clause without aggregate functions.

answered Sep 30, 2013 at 5:59

Szymon

43k16 gold badges99 silver badges115 bronze badges

Comments

Ardalan Shahgholi · Accepted Answer · 2013-09-30 10:32:30Z

All Columns in Projection most be in Group By .

SELECT  registryno ,
    registrystatus ,
    psPatRegisters.pattrantype ,
    PK_psPatRegisters ,
    payername
FROM    psPatRegisters ,
    psPatinv
WHERE   psPatRegisters.PK_psPatRegisters = psPatinv.FK_psPatRegisters
    AND registryno BETWEEN '7133' AND '7169'
    AND psPatinv.pattrantype = 'I'
GROUP BY registryno ,
    registrystatus ,
    psPatRegisters.pattrantype ,
    PK_psPatRegisters ,
    payername

Or Use DISTINCT KeyWord

SELECT  
DISTINCT
    registryno ,
    registrystatus ,
    psPatRegisters.pattrantype ,
    PK_psPatRegisters ,
    payername
FROM    psPatRegisters ,
    psPatinv
WHERE   psPatRegisters.PK_psPatRegisters = psPatinv.FK_psPatRegisters
    AND registryno BETWEEN '7133' AND '7169'
    AND psPatinv.pattrantype = 'I'

and i think if you want only delete duplicate value from your result , best way is use DISTINCT but if you want use aggregate function (Like Sum/Min/...) you most use Group By

Collectives™ on Stack Overflow

How can I Select Distinct values with multiple columns and tables in SQL server?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related