CODEWITHSUNDEEP
sqlpostgresql
user2641077
user2641077

Reputation: 159

How to compare two rows in postgresql?

I have a table tab that contaions:

item identifier quantity methodid
10      1         20         2
10      1         30         3
11      1         10         3
11      1         12.5       3
11      2         20         5
12      1         20         1
12      1         30         1

I need to write a function that checks if there is a case of duplicate methodid for item and identifier. In the above example item 11 identifier 1 has two rows of methodid 3 means it's duplicated, also item 12 idfentifier 1 has duplicated rows as well.

I don't need to do anything to the data just to identify this situation.

I don't need to find where and what was duplicated... just tell there is duplication.

The only information I have is the identifier

  CREATE OR REPLACE FUNCTION func(identifier integer)
      RETURNS integer AS
    $BODY$
    declare
    errorcode int;
    begin
        if _____________   then
            errorcode =1;
            raise exception 'there id duplication in this identifier';  
        END IF;

        continue work

        return 0;           
        exception
            when raise_exception then
    return errorcode;
    end;                
    $BODY$
    LANGUAGE plpgsql VOLATILE

in the blank spot I want to put a query that checks for duplications.

How do I write a query that perform the check? The structure of function can be changed. but I need somehow to know when to raise the exception.

Upvotes: 0

Views: 20017

Answers (4)

user3600910
user3600910

Reputation: 3109

select *
from ( select item,identifier,quantity,methodid, 
        row_number() over(partition item,identifier,methodid) as rank)

each rank row with value higher than 1 is a duplicated row

Upvotes: 1

Phani
Phani

Reputation: 93

Try with this following one may be you will get your result set.

First generate a row number for the table which we have.

For that the following is the query.

select *,ROW_NUMBER() over (partition by item,identifier,methodid order by item) as RowID

from tab;

Then you will get the result like below.

Item   Identifier  quantity  methodid  RowID

10         1        20          2       1

10         1        30          3       1    

11         1        10          3       1    

11         1        12.5        3       2    

11         2         20         5       1    

12         1         20         1       1    

12         1         30         1       2    

12         1         40         2       1

So from this result set you can try with following query,then you will get the result

select * from (
select *,ROW_NUMBER() over (partition by item,identifier,methodid order by item) as rowid
from tab) as p
where p.rowid = 1

Thanks.

Upvotes: 2

user42
user42

Reputation: 96

To check wether any datasets are duplicated based on selected columns you could group by these columns and count the occurrences.

So in your case you could do:

SELECT 1 FROM tab GROUP BY item, identifier, methodid HAVING COUNT(*) > 1;

To incorporate this into your functions you could just check if it exists:

if EXISTS (SELECT 1 ...) then

Upvotes: 5

jarlh
jarlh

Reputation: 44795

Use group by:

select item, identifier, methodid, count(*)
from tab
group by item, identifier, methodid
having count(*) > 1

Where having count(*) > 1 is used to return only duplicated rows.

Upvotes: 2

Related Questions