Select rows with same id but different value in another column

Question

I tried for hours and read many posts but I still can t figure out how to handle this request   I have a table like this    ------ ------   ARIDNR LIEFNR   ------ ------   1      A        ------ ------   2      A        ------ ------   3      A        ------ ------   1      B        ------ ------   2      B        ------ ------    I would like to select the ARIDNR that occurs more than once with the different LIEFNR   The output should be something like    ------ ------   ARIDNR LIEFNR   ------ ------   1      A        ------ ------   1      B        ------ ------   2      A        ------ ------   2      B        ------ ------

User · Answer

Join the same table back to itself   Use an inner join so that rows that don t match are discarded   In the joined set  there will be rows that have a matching ARIDNR in another row in the table with a different LIEFNR   Allow those ARIDNR to appear in the final set   SELECT   FROM YourTable WHERE ARIDNR IN       SELECT a ARIDNR FROM YourTable a     JOIN YourTable b on b ARIDNR   a ARIDNR AND b LIEFNR  lt  gt  a LIEFNR

User · Answer

Select A ARIDNR A LIEFNR from Table A join Table B on A ARIDNR   B ARIDNR and A LIEFNR lt  gt  B LIEFNR group by A ARIDNR A LIEFNR

User · Answer

Use this  select   from    SELECT ARIDNR LIEFNR row number   over        partition by ARIDNR order by ARIDNR  as RowNum  a where a RowNum  gt 1

User · Answer

This ought to do it   SELECT   FROM YourTable WHERE ARIDNR IN       SELECT ARIDNR     FROM YourTable     GROUP BY ARIDNR     HAVING COUNT     gt  1     The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1  times in the data  then get all columns from the same table based on that set of values

User · Answer

sql  SELECT   FROM TABLE NAME WHERE item id    item id    query mysql query  sql    while  myrow mysql fetch array  query      echo   print r  myrow 1

User · Answer

Try this please  I checked it and it s working   SELECT   FROM Table WHERE ARIDNR IN       SELECT ARIDNR     FROM Table     GROUP BY ARIDNR     HAVING COUNT distinct LIEFNR   gt  1

User · Answer

You can simply achieve it by  SELECT   FROM test WHERE ARIDNR IN      SELECT ARIDNR FROM test      GROUP BY ARIDNR      HAVING COUNT     gt  1  GROUP BY ARIDNR  LIEFNR    Thanks

User · Answer

This is an old question yet I find that I also need a solution for this from time to time   The previous answers are all good and works well  I just personally prefer using CTE  for example   DECLARE  T TABLE  ARIDNR INT  LIEFNR varchar 5   --table variable for loading sample data INSERT INTO  T  ARIDNR  LIEFNR  VALUES  1  A    2  A    3  A    1  B    2  B    --add your sample data to it WITH duplicates AS --the CTE portion to find the duplicates       SELECT ARIDNR FROM  T GROUP BY ARIDNR HAVING COUNT     gt  1   SELECT t   FROM  T t --shows results from main table INNER JOIN duplicates d on t ARIDNR   d ARIDNR --where the main table can be joined to the duplicates CTE     Yields the following results      1 A   1 B   2 B   2 A

[sql] Select rows with same id but different value in another column

Examples related to sql