Reputation: 159
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
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
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
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
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