June 20, 2008 at 6:17 am
Hi,
Im having a problem where i have to select data where only one field is unique
testdata like this:
DECLARE @vtADRSRT AS TABLE (
id INT unique, adrid INT, TYPE INT, adsnr int)
DECLARE @vtADRES AS TABLE (adrid INT UNIQUE, naam VARCHAR(25))
DECLARE @resul AS TABLE (naam VARCHAR(20), adrid INT, TYPE INT, adsnr int)
INSERT INTO @vtADRES ([adrid],[naam] ) VALUES ( 1, 'mario')
INSERT INTO @vtADRES ([adrid],[naam]) VALUES ( 2, 'eddy')
INSERT INTO @vtADRES ([adrid],[naam] ) VALUES (3, 'mario')
INSERT INTO @vtADRES ([adrid],[naam]) VALUES (4, 'xxx')
INSERT INTO @vtADRSRT ([id],[adrid],[TYPE], adsnr) VALUES ( 1,1,1,10001)
INSERT INTO @vtADRSRT ([id],[adrid],[TYPE], adsnr) VALUES ( 2,2,1,10002)
INSERT INTO @vtADRSRT ([id],[adrid],[TYPE], adsnr) VALUES ( 3,3,5,10001)
INSERT INTO @vtADRSRT ([id],[adrid],[TYPE], adsnr) VALUES ( 4,4,5,90002)
i wish to select :
adrid, naam, type, adsnr where type = 1 Or type = 5 but where if the same adsnr is already in the recordset i wish to see only the adsnr where the type = 1 and exclude the one where type = 5 and so resulting in a unique naam
here lies my problem
result back should give
1,mario,1,10001
2,eddy,1,10002
4, xxx,5, 90002
but gives:
1,mario,1,10001
2,eddy,1,10002
3,mario,5,10001
4, xxx,5, 90002
i i can not seem to find a way in how to manage to get that result back.
I have tried to create 2 temp tables en a cross join afterwards but the subselect givces me al sorts of errors.
I there an easy way around to get to this result and would anybody be so kind to point me in the right direction in howto accomplish this,
tnx in advance,
Eddy
June 20, 2008 at 6:35 am
SELECT
*
FROM
@vtADRES A
INNER JOIN @vtADRSRT B ON A.adrid = B.adrid
AND B.Type = (CASE WHEN B.Type = 1 THEN 1
ELSE (SELECT MIN(X.Type) FROM
@vtADRES Y
INNER JOIN @vtADRSRT X ON Y.adrid = X.adrid
WHERE Y.naam = A.naam)
END)
June 20, 2008 at 7:24 am
Hello Michael,
Tnx for the reply,
The code works perfect !!
Now a little question for performance issues,
We would love to create a view based on this select statement,
Both tables have around 20.000 records ,
Should there any performance issue arise with this kinda query in a production based environment.?
tnx again for the nice query solution, have saved me from hours and hours of searching and trying
June 20, 2008 at 8:08 am
Here are a few options. Depending on the indexing on the tables, they may perform differently. It is hard to tell with table variables and so little data.
They are all essentially correlated sub-queries and may in fact come back with the same execution plans. 20,000 rows is not that much, so they may perform ok, but I am not entirely sure you will get great performance with any of them. All you can do is try them.
SELECT
*
FROM
@vtADRES A
INNER JOIN @vtADRSRT B ON A.adrid = B.adrid
AND B.Type = (CASE WHEN B.Type = 1 THEN 1
ELSE (SELECT MIN(X.Type) FROM
@vtADRES Y
INNER JOIN @vtADRSRT X ON Y.adrid = X.adrid
WHERE Y.naam = A.naam)
END)
SELECT
*
FROM
@vtADRES A
INNER JOIN @vtADRSRT B ON A.adrid = B.adrid
OUTER APPLY (SELECT X.Type FROM @vtADRES Y
INNER JOIN @vtADRSRT X ON Y.adrid = X.adrid
WHERE Y.naam = A.naam AND X.Type < B.Type) Z
WHERE
Z.Type IS NULL
; WITH Excludes (naam, type)
AS (
SELECT TOP 100 PERCENT Y.naam, MAX(X.Type) FROM @vtADRES Y
INNER JOIN @vtADRSRT X ON Y.adrid = X.adrid
GROUP BY Y.naam
HAVING COUNT(*) > 1
ORDER BY 1,2
)
SELECT
*
FROM
@vtADRES A
INNER JOIN @vtADRSRT B ON A.adrid = B.adrid
LEFT JOIN Excludes E ON A.naam = E.naam AND B.Type = E.Type
WHERE
E.naam IS NULL
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply