Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query approach Expand / Collapse
Author
Message
Posted Tuesday, December 03, 2013 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 39, Visits: 133
I can figure a number of Big Hammer methods of doing this job, but I"m trying to improve myself and stop hitting things with hammers.

Given:

USE [tempdb]
GO

CREATE TABLE [Person]
(
IPCode INT
);

CREATE TABLE [Profile]
(
IPCode INT
, ID NVARCHAR(32)
);

INSERT INTO [Person] VALUES (1);
INSERT INTO [Person] VALUES (2);
INSERT INTO [Person] VALUES (3);
INSERT INTO [Person] VALUES (4);

INSERT INTO [Profile] VALUES (1,'AAAA');
INSERT INTO [Profile] VALUES (1,'AAAA');
INSERT INTO [Profile] VALUES (2,'BBBB');
INSERT INTO [Profile] VALUES (2,'BBBB');
INSERT INTO [Profile] VALUES (2,'BBBC');
INSERT INTO [Profile] VALUES (3,'CCCC');
INSERT INTO [Profile] VALUES (3,'CCCC');
INSERT INTO [Profile] VALUES (3,'CCCC');
INSERT INTO [Profile] VALUES (4,'DDDD');
INSERT INTO [Profile] VALUES (4,'DDDA');
INSERT INTO [Profile] VALUES (4,'DDDB');
INSERT INTO [Profile] VALUES (4,'DDDD');
INSERT INTO [Profile] VALUES (4,'DDDD');

SELECT *
FROM [Person] AS p
JOIN [Profile] AS pr
ON [p].[IPCode] = [pr].[IPCode];

DROP TABLE [Person];
DROP TABLE [Profile];

How would I make the query return only the rows from [Profile] with the same [IPCode] and different [ID]'s. i.e. - I should get back:

2,BBBB
2,BBBC
4,DDDD
4,DDDA
4,DDDB

In the application involved [Person] has about 500K rows, about 10K of which have rows in [Profile] and there are rarely more than 10 rows in [Profile] for a [Person].

[Meta-question: is this an effective way to ask this question in this community?]
Post #1519318
Posted Tuesday, December 03, 2013 11:21 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 2:02 PM
Points: 500, Visits: 2,291
This should get you what you need:

WITH 
xxx AS
(
SELECT pe.IPCode AS pe_ip,
pr.IPCode AS pr_ip,
pr.ID,
DENSE_RANK() OVER (PARTITION BY pr.IPCode ORDER BY ID) AS x
FROM Person pe
CROSS APPLY [Profile] pr
WHERE pe.IPCode=pr.IPCode
),
qualifiers AS
(
SELECT DISTINCT pr_ip
FROM xxx
WHERE x>1
)
SELECT q.pr_ip , xxx.ID
FROM qualifiers q
CROSS APPLY xxx
WHERE q.pr_ip=xxx.pr_ip
GROUP BY q.pr_ip, ID

Both tables should also have clustered indexes and non-clustered index on the join keys (e.g. on IP_Code). You would also want to include NULL constraints; it's a best practice to make all your columns not-nullable whenever possible.

[Meta-question: is this an effective way to ask this question in this community?]

Including DDL and sample data is super-helpful. It's good to include any constraints and indexes that are present too (if you have not done so).


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1519336
Posted Tuesday, December 03, 2013 11:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 39, Visits: 133
OK, that's going to take some looking at . I can feel self-improvement coming on.

Many thanks.
Post #1519340
Posted Tuesday, December 03, 2013 11:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
This might do the trick as well.

SELECT DISTINCT IPCode, ID
FROM Profile p
WHERE EXISTS( SELECT IPCode
FROM Profile x
WHERE p.IPCode = x.IPCode
GROUP BY IPCode HAVING COUNT( DISTINCT ID) > 1)




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1519345
Posted Tuesday, December 03, 2013 1:48 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:37 PM
Points: 319, Visits: 1,142
I ran both solutions on your sample data and Luis' solution was WAY faster, almost double. But the solution provided by Alan uses so many different methods to arrive at the same answer it is worth the study to see how it all comes together. 2 ctes, 1 Dense_Rank, and 2 cross applys on a small dataset provide an excellent learning opportunity.

Run the first cte by itself and study its output with cross apply. Then add the 2nd cte. Try it without the x>1 conditional to see why that is important.

Great stuff here.


__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1519401
Posted Tuesday, December 03, 2013 2:49 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:16 PM
Points: 2,763, Visits: 5,915
It's a great thing that you found an answer that will perform correctly and will be fast and simple to understand and an answer that will help you to learn more about different techniques.
I'm sure there are many possible variations to obtain what you need and that's the greatest thing about SQL.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1519423
Posted Tuesday, December 03, 2013 3:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 39, Visits: 133
I'm pretty happy with the responses! I look forward to learning from both of them.
Post #1519426
Posted Tuesday, December 03, 2013 5:37 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,654, Visits: 5,208
And another slightly different way for the sake of it...

;with data as
(
SELECT p.IPCode,pr.ID,count(*) over(partition by p.IPCode) as UID
FROM [Person] AS p
JOIN [Profile] AS pr
ON [p].[IPCode] = [pr].[IPCode]
group by p.IPCode,pr.ID
)
select IPCode,ID
from data
where UID>1



Got it slightly wrong, I realised...


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1519448
    Posted Tuesday, December 03, 2013 5:54 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Yesterday @ 9:05 PM
    Points: 3,594, Visits: 5,104
    Here's another way that avoids DISTINCT:

    SELECT a.IPCode, ID
    FROM Profile a
    JOIN
    (
    SELECT IPCode
    FROM Profile
    GROUP BY IPCode
    HAVING MAX(ID) <> MIN(ID)
    ) b ON a.IPCode = b.IPCode
    GROUP BY a.IPCode, ID;





    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
    Since random numbers are too important to be left to chance, let's generate some!
    Learn to understand recursive CTEs by example.
    Splitting strings based on patterns can be fast!
    Post #1519450
    Posted Friday, December 06, 2013 1:15 PM


    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Friday, April 18, 2014 12:26 AM
    Points: 77, Visits: 299
    I can't rival the other guys on this board, but I am using this as practice for myself and would like (constructive but friendly) feedback on the solution I came up with if anyone is willing. :)

    WITH CTE1 AS
    (
    SELECT IPCode, ID
    FROM [Profile]
    GROUP BY IPCode, ID
    ),

    CTE2 AS
    (
    SELECT IPCode
    FROM CTE1
    GROUP BY IPCode
    HAVING COUNT(*) > 1
    )

    SELECT IPCode, ID
    FROM [Profile]
    WHERE IPCode IN
    (SELECT IPCode FROM CTE2)
    GROUP BY IPCode, ID




    "If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
    Post #1520727
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse