How to select distinct by 2 fields

Question

I need to be able to only select records which have an IsActive = 1 in addition to all of this:

SELECT DISTINCT 0 as CommunityID,  

                    CASE Libname
                        WHEN 'RKHL' THEN 'ROCK HILL'
                        WHEN 'FTML' THEN 'FORT MILL'
                        WHEN 'LANC' THEN 'LANCASTER'
                        WHEN 'BREV' THEN 'BREVARD'
                        WHEN 'PBTC' THEN 'MIDLANDS-CLEC'
                        WHEN 'PBTI' THEN 'MIDLANDS-ILEC'

                    END AS CommunityDesc,

                    LibName,
                    LibName + '|' AS FilterByID,
                    IsActive 
                    FROM Reference.dbo.Community
                    WHERE LibName <> 'CAROTEL' 
                    UNION
                    SELECT CommunityID, RTRIM(COMMNAME) AS CommunityDesc, LibName, LibName + '|' + RTRIM(COMMNAME) AS FilterByID, IsActive
                    FROM Reference.dbo.Community 

                    WHERE LibName <> 'CAROTEL'
                    ORDER BY 1,3;

You should try using a `GROUP BY` for multiple columns, as shown here: http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns-postgresql — rownage, Jun 04 '12 at 19:35
In addition to having the Community ID be 0, I also need all of the data returned to have IsActive to be 1. I have tried to do a SELECT DISTINCT 0 as CommunityID UNION SELECT DISTINCT 1 as IsActive, but that didnt work. I also dont know if GROUP BY is applicable here... — ibolton336, Jun 04 '12 at 19:36

score 1 · Accepted Answer · answered Jun 04 '12 at 19:35

1

Add IsActive=1 to the WHERE clause on both sides of the Union.

answered Jun 04 '12 at 19:35

JohnFx

34,542
18
104
162

score 1 · Answer 2 · answered Jun 04 '12 at 19:37

You can filter the result set in this way :

 SELECT * 
    FROM (
    SELECT DISTINCT 0 as CommunityID,  

                    CASE Libname
                        WHEN 'RKHL' THEN 'ROCK HILL'
                        WHEN 'FTML' THEN 'FORT MILL'
                        WHEN 'LANC' THEN 'LANCASTER'
                        WHEN 'BREV' THEN 'BREVARD'
                        WHEN 'PBTC' THEN 'MIDLANDS-CLEC'
                        WHEN 'PBTI' THEN 'MIDLANDS-ILEC'

                    END AS CommunityDesc,

                    LibName,
                    LibName + '|' AS FilterByID,
                    IsActive 
                    FROM Reference.dbo.Community
                    WHERE LibName <> 'CAROTEL' 
                    UNION
                    SELECT CommunityID, RTRIM(COMMNAME) AS CommunityDesc, LibName, LibName + '|' + RTRIM(COMMNAME) AS FilterByID, IsActive
                    FROM Reference.dbo.Community 

                    WHERE LibName <> 'CAROTEL'
) i
WHERE IsActive = 1
ORDER BY 1,3;

How to select distinct by 2 fields

2 Answers2