CODEWITHSUNDEEP
sql-serversql-server-2008
Ren D. Carganillo
Ren D. Carganillo

Reputation: 1

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

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

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?

Upvotes: 0

Views: 1426

Answers (3)

Ardalan Shahgholi
Ardalan Shahgholi

Reputation: 12555

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

Upvotes: 0

Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

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

Upvotes: 2

Szymon
Szymon

Reputation: 43023

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.

Upvotes: 0

Related Questions