Help Needed please

  • 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

  • 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)

  • 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

  • Thanks Doug, But it does not seem to be filtering out the id's that are single entries.

  • 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 .

  • Sorry - I forgot the crucial "AND t2.ID <> t1.ID". I've corrected my original post.

  • 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

  • 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]')

  • 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![/I]

    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