Distinct Select Query
-
Hi I have table with three columns col1, col2, col3 i need distinct values of col1 with any values in col2 and col3, eg if data is 1, A, A 1, B, B 2, C, C i need my query to bring 1, A, A or 1, B, B (only one not both) 2, C, C i need query for this. i cannot use select dintinct col1, col2, col3 from table due to 2nd row. i am using sql server.
Regards Shajeel
-
Hi I have table with three columns col1, col2, col3 i need distinct values of col1 with any values in col2 and col3, eg if data is 1, A, A 1, B, B 2, C, C i need my query to bring 1, A, A or 1, B, B (only one not both) 2, C, C i need query for this. i cannot use select dintinct col1, col2, col3 from table due to 2nd row. i am using sql server.
Regards Shajeel
Shajeel wrote:
I have table with three columns col1, col2, col3 i need distinct values of col1 with any values in col2 and col3, eg if data is 1, A, A 1, B, B 2, C, C i need my query to bring 1, A, A or 1, B, B (only one not both) 2, C, C
Don't know which version of SQL Server you are using. If 2000 then below written sql will solve the problem. If you are using 2005 then you can use ROW_NUMBER() instead of IDENTITY() and there is no need for #TempTable. If you are using 2000 and table has a Identity column then also there is no need for #TempTable. Identity Column or the higher version (2005) will help you to do in single sql stmt...
select IDENTITY(INT, 1, 1)AS KeyCol, Col1, col2, col3 into #TempTable from tbldistinct select col1,col2,col3 from #TempTable B where (Select Count(*) from #TempTable A where A.col1 = B.col1 and A.KeyCol <= B.KeyCol) =1 Drop Table #TempTableRegards
J O H N :rose:
"Even eagles need a push." David McNally
-
Hi I have table with three columns col1, col2, col3 i need distinct values of col1 with any values in col2 and col3, eg if data is 1, A, A 1, B, B 2, C, C i need my query to bring 1, A, A or 1, B, B (only one not both) 2, C, C i need query for this. i cannot use select dintinct col1, col2, col3 from table due to 2nd row. i am using sql server.
Regards Shajeel
Use a groupby would be the easiest why without DISTINCT
select col1, col3, col3 from table ORDER BY col1, col3, col3Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture) http://www.frankkerrigan.com/[^]
-
Shajeel wrote:
I have table with three columns col1, col2, col3 i need distinct values of col1 with any values in col2 and col3, eg if data is 1, A, A 1, B, B 2, C, C i need my query to bring 1, A, A or 1, B, B (only one not both) 2, C, C
Don't know which version of SQL Server you are using. If 2000 then below written sql will solve the problem. If you are using 2005 then you can use ROW_NUMBER() instead of IDENTITY() and there is no need for #TempTable. If you are using 2000 and table has a Identity column then also there is no need for #TempTable. Identity Column or the higher version (2005) will help you to do in single sql stmt...
select IDENTITY(INT, 1, 1)AS KeyCol, Col1, col2, col3 into #TempTable from tbldistinct select col1,col2,col3 from #TempTable B where (Select Count(*) from #TempTable A where A.col1 = B.col1 and A.KeyCol <= B.KeyCol) =1 Drop Table #TempTableRegards
J O H N :rose:
"Even eagles need a push." David McNally