December 11, 2012 at 7:46 pm
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
December 11, 2012 at 8:23 pm
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)
December 11, 2012 at 8:24 pm
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
December 11, 2012 at 8:36 pm
Thanks Doug, But it does not seem to be filtering out the id's that are single entries.
December 11, 2012 at 8:37 pm
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 .
December 11, 2012 at 8:49 pm
Sorry - I forgot the crucial "AND t2.ID <> t1.ID". I've corrected my original post.
December 11, 2012 at 9:42 pm
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
December 12, 2012 at 2:50 pm
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]')
December 12, 2012 at 5:47 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply