Retrieve duplicate and non duplicate query
-
Hi, I have employee table. ID empno salary empname 1 1 5000 sdf 2 2 4000 sdf 3 3 4000 sdfas 4 4 4500 sdf 5 5 5000 k 6 8 1000 TT I tried to retrieve the non duplicate rows from the using. Ex: the output should be ID empno salary empname 1 1 5000 sdf 2 2 4000 sdf 4 4 4500 sdf 6 8 1000 TT It should not be display the duplicate values in the salary. Thanks Sujatha
-
Hi, I have employee table. ID empno salary empname 1 1 5000 sdf 2 2 4000 sdf 3 3 4000 sdfas 4 4 4500 sdf 5 5 5000 k 6 8 1000 TT I tried to retrieve the non duplicate rows from the using. Ex: the output should be ID empno salary empname 1 1 5000 sdf 2 2 4000 sdf 4 4 4500 sdf 6 8 1000 TT It should not be display the duplicate values in the salary. Thanks Sujatha
SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM tblName GROUP BY Id HAVING COUNT(Id) = 1)) --> This query gives you a non duplicate rows SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM tblName GROUP BY Id HAVING COUNT(Id) > 1)) --> This query gives you a duplicate rows
-
Hi, I have employee table. ID empno salary empname 1 1 5000 sdf 2 2 4000 sdf 3 3 4000 sdfas 4 4 4500 sdf 5 5 5000 k 6 8 1000 TT I tried to retrieve the non duplicate rows from the using. Ex: the output should be ID empno salary empname 1 1 5000 sdf 2 2 4000 sdf 4 4 4500 sdf 6 8 1000 TT It should not be display the duplicate values in the salary. Thanks Sujatha
here it is:
SELECT (SELECT TOP 1 t1.id FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS id , (SELECT TOP 1 t1.empno FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empno, salary, (SELECT TOP 1 t1.empname FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empname FROM dbo.TableName GROUP BY salary ORDER BY id ASC
I Love T-SQL "Don't torture yourself,let the life to do it for you."
-
SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM tblName GROUP BY Id HAVING COUNT(Id) = 1)) --> This query gives you a non duplicate rows SELECT * FROM tblName WHERE (Id IN (SELECT Id FROM tblName GROUP BY Id HAVING COUNT(Id) > 1)) --> This query gives you a duplicate rows
-
here it is:
SELECT (SELECT TOP 1 t1.id FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS id , (SELECT TOP 1 t1.empno FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empno, salary, (SELECT TOP 1 t1.empname FROM dbo.TableName AS t1 WHERE t1.salary = dbo.TableName.salary) AS empname FROM dbo.TableName GROUP BY salary ORDER BY id ASC
I Love T-SQL "Don't torture yourself,let the life to do it for you."
-
I tried to execute the above query in the msacess. it throws an error msg as you tried to execute a query that does not include the specified expression 'id' as part of an aggregate function Kindly help ASAP.
-
remove line
ORDER BY id ASC
I Love T-SQL "Don't torture yourself,let the life to do it for you."