CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
veljasije
veljasije

Reputation: 7092

MIN() aggregate function with some criteria

I have table Places with following columns:

PlaceId     CityId     Name       Postcode
-----------------------------------------
1           1          Place1     NULL
2           1          Place1     10000
3           2          Place1     10300
4           2          Place2     10500
5           3          Place3     NULL
6           3          Place3     NULL

Output I want is:

PlaceId     CityId     Name       Postcode
-----------------------------------------
2           1          Place1     10000
3           2          Place1     10300
4           2          Place2     10500
5           3          Place3     NULL

So, I need following:

If one city has duplicates over CityId and Name columns, then I need one row with minimal PlaceId. But, if first of two duplicates has NULL in Postcode column, and bigger id has some value in same column, then I need second row in output (example, rows with ids 1 and 2, returned row with id 2). In case that all duplicates has NULL value in column Postcode, then just return minimal PlaceId (example, rows with ids 5 and 6, returned row with id 5)

So, column Postcode affect in final output

I've tried like this:

SELECT 
        MIN(nm.PlaceId) AS PlaceId,
        nm.CityId,
        nm.name,        
        COUNT(*) AS Number
    FROM dbo.Place AS nm
    GROUP BY
        nm.CityId ,
        nm.name

I can solve this problem, but solution is not nice, and I am asking for some nice and elegant solution.

Upvotes: 0

Views: 74

Answers (2)

fastobject
fastobject

Reputation: 1430

Following query should help for you:

;WITH Places as 
(
    select 1 as PlaceId, 1 as CityId, 'Place1' as Name, NULL as Postcode
    UNION ALL 
    select 2, 1, 'Place1', 10000
    UNION ALL 
    select 3, 2, 'Place1', 10300
    UNION ALL 
    select 4, 2, 'Place2', 10300
    UNION ALL 
    select 5, 3, 'Place3', NULL
    UNION ALL 
    select 6, 3, 'Place3', NULL
)
SELECT t2.PlaceId, groups.CityId, groups.Name, t2.Postcode FROM (
    select CityId, Name from Places GROUP BY CityId, Name
    ) groups 
    CROSS APPLY  
    (
        SELECT TOP(1) 
          d2.PlaceId, d2.Postcode, 
          CASE WHEN d2.Postcode IS NOT NULL THEN 1 ELSE 2 END AS RecordPriority 
        FROM Places d2 
        WHERE d2.CityId = groups.CityId AND d2.Name = groups.Name 
        ORDER BY RecordPriority, PlaceId
    ) t2 ;

or using ROW_NUMBER() with the same ORDER BY as above:

;WITH Places AS 
(
...
),
OrderedPlaces AS
(
    SELECT *, 
        Rn = ROW_NUMBER() OVER
            (PARTITION BY CityId, Name
             ORDER BY CASE WHEN Postcode IS NOT NULL THEN 1 ELSE 2 END, 
                      PlaceID)
    FROM Places  
)
SELECT PlaceId, CityId, Name, Postcode 
FROM OrderedPlaces
WHERE Rn = 1 ;

Upvotes: 1

singhswat
singhswat

Reputation: 906

how about this ...

CREATE TABLE #PLACES
( PlaceId int, CityId int, Name varchar(50), Postcode varchar(50)
)
INSERT INTO #PLACES (PlaceId, CityId, Name)VALUES(1, 1, 'Place1' )
INSERT INTO #PLACES (PlaceId, CityId, Name, Postcode)VALUES(2, 1, 'Place1','10000' )
INSERT INTO #PLACES (PlaceId, CityId, Name, Postcode)VALUES(3, 2, 'Place1' , '10300')
INSERT INTO #PLACES (PlaceId, CityId, Name, Postcode)VALUES(4, 2, 'Place2','10500' )
INSERT INTO #PLACES (PlaceId, CityId, Name)VALUES(5, 3, 'Place3' )
INSERT INTO #PLACES (PlaceId, CityId, Name)VALUES(6, 3, 'Place3' )

--- Query

;WITH CTE_RESULTS AS
(
    SELECT ROW_NUMBER() OVER (PARTITION BY CityId, Name ORDER BY Postcode DESC) ROW_NO, PlaceId , CityId, Name, Postcode
    FROM #PLACES
)
SELECT * FROM CTE_RESULTS WHERE ROW_NO=1

Upvotes: 1

Related Questions