Need to return an ID which has start and END in sql server

Question

I have a scenario wherein I need to find the ID which only has start and END in it. Below is the table for reference.

Declare @T Table ( ID int, Name varchar(100))

Insert into @T values (1,'Start')
Insert into @T values (1,'END')
Insert into @T values (1,'Stuart')
Insert into @T values (1,'robin')
Insert into @T values (2,'Start')
Insert into @T values (2,'END')
Insert into @T values (3,'Start')
Insert into @T values (4,'END')

I want the Output as:

ID Name
2 Start
2 END

I want those ID which only has start and end in it.

What I tried so far:

SELECT * FROM @T t 
WHERE EXISTS (SELECT * FROM @T WHERE id = t.id AND name = 'start')
AND EXISTS (SELECT * FROM @T WHERE id = t.id AND name = 'END')

But my query is giving ID 1 as well.

Can someone please help me rectify the problem.

is your sql server case insensitive? Because by default it is. You can ask Admin to run that off and the retry. Or you can use `dbo.InitCap()` function to match to `Start`. — Koushik Roy, Oct 01 '20 at 05:49
in that case you can use `COLLATE ` to make it work. Pls use the sql in the answer and let me know. — Koushik Roy, Oct 01 '20 at 05:53
@KoushikRoy or not. Definitely not. This isn't a collation issue, it's a quirky query. Breaking how an entire database works is *not* a solution. Changing a database's collation won't change the existing column collations either. Forcing a collation prevents the use of any existing indexes. Besides, a cases-*in*sensitive search would match both `end` and `END` without any problems. A case-*sensitive* search could bypass records — Panagiotis Kanavos, Oct 01 '20 at 06:31
i dont know if having a database with case insensitivity is a good idea. But someone in must have MS thought it through. — Koushik Roy, Oct 01 '20 at 08:16

seanb · Accepted Answer · 2020-10-01T06:09:25.237

I presume your issue is that record 1 has a 'Stuart' in it too?

As such, you can do a similar check in the WHERE e.g.,

SELECT * FROM @T t 
WHERE EXISTS (SELECT * FROM @T WHERE id = t.id AND name = 'start')
AND EXISTS (SELECT * FROM @T WHERE id = t.id AND name = 'END')
AND NOT EXISTS (SELECT * FROM @T WHERE id = t.id AND name NOT IN ('start','END'))

Note that you may want to consider

What happens if you have two 'start' rows or two 'end' rows (e.g., start-start-end)? Can you even have two 'start' rows (e.g., start-start)?
What happens if you have a blank/NULL (e.g., start-NULL-end)?

EDIT: removed 'What happens if they're out of order (e.g., end-start)?' as a question as there is no sorting in the data at all (e.g., not even an implicit sort).

Note that @Pwilcox had another answer that was more efficient than my answer above (as only one scan is needed) if the data requirements were met. The best answer will depend heavily on how you answer the questions included above. — seanb, Oct 01 '20 at 06:08

score 0 · Answer 2 · answered Oct 01 '20 at 06:29

You can go for CTE to get group wise count and total count as 2.

Declare @T Table ( ID int, Name varchar(100))

Insert into @T values (1,'Start')
Insert into @T values (1,'END')
Insert into @T values (1,'Stuart')
Insert into @T values (1,'robin')
Insert into @T values (2,'Start')
Insert into @T values (2,'END')
Insert into @T values (3,'Start')
Insert into @T values (4,'END')

;WITH CTE_Total_StartEnd AS
(
select id, count(*) AS Total_Cnt
, COUNT( case when Name IN ('Start') THEN 1 END) as start_cnt 
, COUNT( case when Name IN ('End') THEN 1 END) as end_cnt 
from @t
group by id
having  COUNT( case when Name IN ('Start') THEN 1 END) =1 and
COUNT( case when Name IN ('End') THEN 1 END) = 1  and
count(*) = 2
)
SELECT t.* from @t t
inner join CTE_Total_StartEnd as c 
ON c.id = t.id

+----+-------+
| ID | Name  |
+----+-------+
|  2 | Start |
|  2 | END   |
+----+-------+

score 0 · Answer 3 · answered Oct 01 '20 at 08:57

You can do this by using group by function also like below

WITH cte AS 
(
SELECT 1 AS id , 'Start' AS name 
UNION ALL 
SELECT 1  AS id ,'END'  AS name 
UNION ALL
SELECT 1 AS id ,'Stuart'  AS name
UNION ALL
SELECT 1 AS id ,'robin'  AS name  
UNION ALL
SELECT 2  AS id ,'Start'  AS name 
UNION ALL
SELECT 2 AS id ,'END'  AS name
UNION ALL
SELECT 3 AS id ,'Start'  AS name  
UNION ALL
SELECT 4 AS id ,'END'  AS name 
)
SELECT T.ID,SUM(T.VAL)AS SUM
FROM 
(
SELECT  id,name , CASE WHEN name='Start' THEN 1
                       WHEN name='END' THEN  2
                       ELSE 3
                  END AS VAL 
                    
FROM cte 
)T 
GROUP BY T.ID 
HAVING SUM(T.VAL) =3

Koushik Roy · Answer 4 · 2020-10-01T05:59:58.947

-1

could you please try this? Pls note i added collate command in the end of sql. SQL Server check case-sensitivity?

SELECT * FROM @T t 
WHERE EXISTS (SELECT * FROM @T WHERE id = t.id AND name = 'start' COLLATE SQL_Latin1_General_CP1_CS_AS)
AND EXISTS (SELECT * FROM @T WHERE id = t.id AND name = 'END' COLLATE SQL_Latin1_General_CP1_CS_AS)

edited Oct 01 '20 at 05:59

answered Oct 01 '20 at 05:52

Koushik Roy

6,868
2
12
33

Msg 156, Level 15, State 1, Line 21 Incorrect syntax near the keyword 'COLLATE'. – Red Devil Oct 01 '20 at 05:56
could you please try this. I will get back with correct sql later because i can not test this out now. – Koushik Roy Oct 01 '20 at 06:00
Forcing a case-sensitive collation would *miss* all `Start` records. It would create a *bigger* problem, not solve the current one. Forcing the collation prevents the server from using any indexes, so this query will result in a full table scan – Panagiotis Kanavos Oct 01 '20 at 06:35
that was just a suggestion in comments and not a part of my answer. as i said i need to test this to ensure it works but it will give an idea. – Koushik Roy Oct 01 '20 at 08:18

Need to return an ID which has start and END in sql server

4 Answers4