Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can anyone advise how do I check multiple rows with same data?

E.g.

I have 2 rows with following values:

Cola         Colb  Colc
TYPE_APPLE   123   abc
Colb         Colb  Colc
TYPE_APPLE   123   abc

I want to write an SQL query which will not select data of multiple rows which contain the same data as above. How should I write the query?

share|improve this question
2  
It's very simple just search for group by, and count() and you will find your anwer –  Maryam Arshi Feb 27 '13 at 6:29
    
@PaulWhite As I understand till now the base rule of Stackexchange is not to ask questions which can be solved by a search. Yes it was easy to write an answer but I wanted this user at least try to find an answer himself(herself) not just request for a ready answer –  Maryam Arshi Feb 28 '13 at 5:43
add comment

3 Answers

Use this query:

SELECT DISTINCT cola,colb,colc FROM table_name
share|improve this answer
add comment

DISTINCT will combine identical rows. GROUP BY will combine them and allow you to know which ones had multiples using COUNT(*). To meet your "not select data of multiple rows" requirement you will need to add a HAVING clause. Your query will look something like this:

SELECT cola, colb, colc FROM T1 GROUP BY cola, colb, colc HAVING count(*) = 1;

SQL Fiddle Demonstration

share|improve this answer
    
This will completely remove the duplicate records e.g. your output has 4 rows as opposed to 5 rows. Your code will remove TYPE_APPLE 123 abc completely. –  Kin Dec 16 '13 at 18:13
    
I understood "not select data of multiple rows" to mean that no rows identical to another should be included. This would apply to both the the first (being duplicate of the second) and the second (being a duplicate of the first). If the intention intention of the OP (or the reader) was to eliminate only the secondary instance of the row, then an analytic like yours would be the route to go. (+1) –  Leigh Riffel Dec 16 '13 at 18:41
add comment

For the sake of completeness, I would add using ROW_NUMBER() OVER PARTITION BY method :

I will use the same fiddle written by Leigh Riffel.

CREATE TABLE T1 (Cola Varchar(20), Colb Varchar(10), Colc Varchar(10));

INSERT INTO T1 VALUES ('TYPE_APPLE','123','abc');
INSERT INTO T1 VALUES ('TYPE_ORANGE','456','def');
INSERT INTO T1 VALUES ('TYPE_APPLE','123','abc');
INSERT INTO T1 VALUES ('TYPE_ORANGE','123','abc');
INSERT INTO T1 VALUES ('TYPE_APPLE','567','abc');
INSERT INTO T1 VALUES ('TYPE_APPLE','123','xyz');

--- below will give you the table with just single rows - non duplicates
;WITH CTE AS 
( 
SELECT ROW_NUMBER() OVER 
(PARTITION BY [Cola], [Colb], [Colc] 
Order BY [Cola] desc, [Colb] desc, [Colc] desc ) 
AS RowNumber, 
[Cola], [Colb], [Colc]
FROM T1 tbl ) 
SELECT [Cola], [Colb], [Colc] FROM CTE where RowNumber = 1
GO
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.