Geek asks and answers

Sql server select query one column is the same and another is another

I want to be able to find occurrences in a table where one column is the same but the other is different.

Example table:

id    team    week
1     1       1
1     2       1
2     2       1
2     1       2

I need a query that finds all the identifiers in which the team is different, and the week is still the same, something like a team is not equal to the team, but the week is equal to the week.

Basically, I would like to know if any team ID changed in the same week, how do I do this?

+5
source share
3 answers
SELECT 
    t1.id, 
    t1.week
FROM 
    YourTable t1
    JOIN YourTable t2 
        ON t1.ID = t2.ID
        AND t1.team < t2.team
        AND t1.week = t2.week
+6
source 
SELECT ID
, COUNT(DISTINCT TEAM) AS CNT_TEAM
, COUNT(DISTINCT WEEK) AS CNT_WEEK
FROM TABLENAME
GROUP BY ID
HAVING COUNT(DISTINCT TEAM) > 1
AND COUNT(DISTINCT WEEK) = 1
0
source

Something like that?

SELECT distinct id
FROM TeamWeek
GROUP BY id, week
HAVING Count(team) > 1
0
source

More articles:


All Articles