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

Help Needed please Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 7:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 2:53 PM
Points: 5, Visits: 17
Hi. I have a table with figures below.

003223
003225
003227
003227A
003236
003236A
003241
003273
003273A

I need to do a search to find all items where the id is like other id's and exclude all others.

So i need to list

003227
003227A
003236
003236A
003273
003273A

Post #1395396
Posted Tuesday, December 11, 2012 8:23 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 30, 2014 7:56 PM
Points: 29, Visits: 86
Assumed that field name is ID and that it is the first 6 characters is the portion you are looking at
I loaded it all into a temp table and used:
Select ID from #Temp where Left(id,6) IN (Select left(id,6) from #Temp group by left(id,6) having count(*) > 1)

Post #1395401
Posted Tuesday, December 11, 2012 8:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 10:11 AM
Points: 18, Visits: 71
Hi terryphone,

If the data is reliably as you listed (the data is alphanumeric and the "like" IDs are always 6 numeric digits with or without a letter appended), you should be able to get your list from the following query:

SELECT ID                              --I'm assuming the column name is ID, substitute the true column name
FROM TableName t1 --I'm giving your table this generic table name, substitute the true table name
WHERE EXISTS (
SELECT *
FROM TableName t2 --substitute the true table name here as well
WHERE LEFT(t2.ID,6) = LEFT(t1.ID,6) --substitute the true column name here as well
AND t2.ID <> t1.ID
)
ORDER BY ID


Hope that helps.

--Doug
Post #1395402
Posted Tuesday, December 11, 2012 8:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 2:53 PM
Points: 5, Visits: 17
Thanks Doug, But it does not seem to be filtering out the id's that are single entries.
Post #1395404
Posted Tuesday, December 11, 2012 8:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 30, 2014 7:56 PM
Points: 29, Visits: 86
Hey Terry unless I understood wrong I am not sure that Dougs solution actually does what you want. Sorry Doug, not sure you tested your code or perhaps you mistyped something.
The code I gave gives 6 rows, Doug yours gave 9.
NOTE: I'm new to this so when I posted my T-SQL it still converted the > (greater than sign) to the escaped characters, ah well .


Post #1395405
Posted Tuesday, December 11, 2012 8:49 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 7, 2014 10:11 AM
Points: 18, Visits: 71
Sorry - I forgot the crucial "AND t2.ID <> t1.ID". I've corrected my original post.
Post #1395408
Posted Tuesday, December 11, 2012 9:42 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:53 PM
Points: 245, Visits: 1,131
Like wise, assuming you are looking at the first 6 characters.

SELECT ID FROM
(SELECT SUBSTRING(ID,1,6) ID,COUNT(1)x FROM #T
GROUP BY SUBSTRING(ID,1,6))D WHERE D.x>1
Post #1395417
Posted Wednesday, December 12, 2012 2:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, December 13, 2012 2:53 PM
Points: 5, Visits: 17
Thanks for your help everyone. Did some further work last night and came up with a solution for my needs.


SELECT a.ur_number, b.ur_number, c.name_last, c.name_first
FROM SYS_CLI_Client a
INNER JOIN SYS_CLI_Client b ON (SUBSTRING(a.ur_number, 1, LEN(a.ur_number) - 1)=b.ur_number)
INNER JOIN SYS_GEN_Person c on (b.person_id = c.id)
WHERE a.ur_number LIKE ('%[A-Z]')
Post #1395894
Posted Wednesday, December 12, 2012 5:47 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:53 PM
Points: 3,438, Visits: 5,390
I like to advise people to avoid self-JOINs whenever possible because I've always found them to be performance dogs across large row sets. Woof!

Not sure this is faster than the EXISTS or IN solutions proposed earlier, but it will almost certainly be swifter than a self JOIN.

DECLARE @T TABLE (ProductID VARCHAR(50))

INSERT INTO @T
SELECT '003223' UNION ALL SELECT '003225' UNION ALL SELECT '003227'
UNION ALL SELECT '003227A' UNION ALL SELECT '003236' UNION ALL SELECT '003236A'
UNION ALL SELECT '003241' UNION ALL SELECT '003273' UNION ALL SELECT '003273A'

SELECT ProductID
FROM (
SELECT ProductID, m=MAX(n) OVER (PARTITION BY LEFT(ProductID, 6))
FROM (
SELECT ProductID
,n=ROW_NUMBER() OVER (PARTITION BY LEFT(ProductID, 6) ORDER BY (SELECT NULL))
FROM @T) a) b
WHERE m > 1


Anyway, that is another alternative you can consider.



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 #1395948
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse