0

Please login or register to vote for this query.

(click on this box to dismiss)

This is test data for my question to make things easier for answerers to test (hopefully).

Q&A for people looking to improve their interpersonal communication skills

-- Test Data for SO Question
-- This is test data for my question to make things easier for answerers to test (hopefully).

CREATE TABLE #deals
(
    nid int NOT NULL,
    city nvarchar(50) NULL,
    prov nvarchar(50) NULL,
)

INSERT INTO #deals(nid, city, prov)
VALUES 
(16242,'warszawa', 'a'),
(16241,'krakow', 'a'),
(16235,'krakow', 'a'),
(16238,'wroclaw', 'b'),
(16243,'wroclaw', 'b'),
(16237,'wroclaw', 'b'),
(16234,'wroclaw', 'b'),
(16236,'poznan', 'a'),
(16239,'poznan', 'a'),
(16240,'poznan', 'a')

SELECT c1.nid, c1.city, c1.prov
FROM #deals c1 LEFT JOIN #deals c2
    ON (c1.city = c2.city and c1.nid >= c2.nid)
WHERE c1.prov != 'b'
GROUP BY c1.city, c1.nid, c1.prov
HAVING COUNT(*) <= 3

fork query

created dec 3 11 anonymous