SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help Needed please


Help Needed please

Author
Message
terryphone
terryphone
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
ShineBoy
ShineBoy
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 155
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)


doug.baker-706021
doug.baker-706021
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 81
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
terryphone
terryphone
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 17
Thanks Doug, But it does not seem to be filtering out the id's that are single entries.
ShineBoy
ShineBoy
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 155
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 .
doug.baker-706021
doug.baker-706021
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 81
Sorry - I forgot the crucial "AND t2.ID <> t1.ID". I've corrected my original post.
peacesells
peacesells
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 1481
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
terryphone
terryphone
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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]')
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)SSCertifiable (7.4K reputation)

Group: General Forum Members
Points: 7399 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search