CODEWITHSUNDEEP
sqlt-sql
Mister Epic
Mister Epic

Reputation: 16733

Return all records where a field has the same prefix

I have a PromoID field in a table. I need to figure out how to display all records where the first 6 charactes are the same:

PromoID
=======
100001
100001A
100001B
101001
100002
100002A

The result I would expect to see from the above would be:

PromoID
=======
100001
100001A
100001B
100002
100002A

101001 gets excised as it doesn't have another record with the "101001" prefix.

Thanks

Upvotes: 0

Views: 174

Answers (5)

Mike Christensen
Mike Christensen

Reputation: 91628

You can do this with a Common Table Expression:

WITH CTE (Prefix) AS
  (select LEFT(PromoId, 6) from Foo GROUP BY LEFT(PromoId, 6) HAVING COUNT(1) > 1)
SELECT PromoId FROM Foo
INNER JOIN CTE ON LEFT(PromoId, 6) = CTE.Prefix;

Fiddle

Upvotes: 0

manji
manji

Reputation: 47978

The inner query lists all PromoID to reject. The outer query takes all PromoID minus the rejected ones:

SELECT *
  FROM table1
 WHERE PromoID not in (
    SELECT t1.PromoID
      FROM table1 t1
    LEFT JOIN table1 t2 ON t1.PromoID != t2.PromoID
          AND left(t1.PromoID, 6) = left(t2.PromoID, 6)
     WHERE t2.PromoID IS NULL
 )

SQL Fiddle

Upvotes: 0

Fabian Bigler
Fabian Bigler

Reputation: 10895

You can also do this using a subselect

SELECT PromoID FROM YourTable
WHERE LEFT(PromoID,6) IN
(
SELECT LEFT(PromoID,6) 
FROM YourTable 
Group By LEFT(PromoID, 6)
HAVING COUNT(*) > 1
)

Fiddle Demo

Upvotes: 1

Andriy M
Andriy M

Reputation: 77687

If you are using SQL Server 2005 or later version, this should work for you:

SELECT PromoID
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY LEFT(PromoID, 6)) AS cnt
  FROM atable
) s
WHERE cnt > 1
;

You can try this at SQL Fiddle too.

Upvotes: 6

Lamak
Lamak

Reputation: 70648

SELECT PromoID
FROM YourTable A
WHERE EXISTS(SELECT LEFT(PromoID,6)
             FROM YourTable
             WHERE LEFT(PromoID,6) = LEFT(A.PromoID,6)
             GROUP BY LEFT(PromoID,6)
             HAVING COUNT(*) > 1)

This are the results:

╔═════════╗
║ PromoID ║
╠═════════╣
║ 100001  ║
║ 100001A ║
║ 100001B ║
║ 100002  ║
║ 100002A ║
╚═════════╝

Here is an sqlfiddle with a demo.

Upvotes: 8

Related Questions