asked on How to get the only repeating record from the table
I have a table how can i return only the repeating records in the table where productID and orderID column sboth have repeating values both are same.
Table structure:
Name : returnproducts
columns :
returnproductid
dateadded
productid
orderid
Thanks
Thanks
Table structure:
Name : returnproducts
columns :
returnproductid
dateadded
productid
orderid
Thanks
Thanks
Microsoft SQL ServerMicrosoft SQL Server 2005SQL
Last Comment
shieldguy
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
chapmandew: Query given me 12 records
aneeshattingal: query given me 30 records
which one is correct
aneeshattingal: query given me 30 records
which one is correct
it depends on what you want.
My query gives you a unique list of products and orders that appear more than one time
aneeshattingal's query gives you the list of all records where that combination appears more than one time.
My query gives you a unique list of products and orders that appear more than one time
aneeshattingal's query gives you the list of all records where that combination appears more than one time.
ASKER
actually i also need to find out the returnproductid which should be unique anyway
>actually i also need to find out the returnproductid which should be unique anyway
that means there are 30 records :)
that means there are 30 records :)
Then this should work:
select productid, orderid, MIN(returnproductid)
from tablename
group by productid, orderid
having count(*) > 1
select productid, orderid, MIN(returnproductid)
from tablename
group by productid, orderid
having count(*) > 1
it doesn't mean that there are 30 unique records though.
ASKER
actually whats happening while inserting the records in to this table some how the records with same productid and orderid are entering in to the database and so now i need to filter them all out and delete them
All of them, or all of them, but one?
do you have a field that uniquely identifies the records in your table (primary key)?
ASKER
returnproductid is the primary key
also if we have 3 records of product id = 33
and orderid = 44
then i need to see all these 3 records with their returnproductid beacuse returnproductid will be uniquie for all 3 of them and so i can delete them
also if we have 3 records of product id = 33
and orderid = 44
then i need to see all these 3 records with their returnproductid beacuse returnproductid will be uniquie for all 3 of them and so i can delete them
You can try my last post or change aneeshattingal's solution to:
SELECT r.*
FROM ...
SELECT r.*
FROM ...
If you want to delete all 3 records, then:
delete r
from returnproducts r
join
(
select productid, orderid
from tablename
group by productid, orderid
having count(*) > 1
) a on r.productid = a.productid and r.orderid = a.orderid
delete r
from returnproducts r
join
(
select productid, orderid
from tablename
group by productid, orderid
having count(*) > 1
) a on r.productid = a.productid and r.orderid = a.orderid
This would delete records with duplicate productid and orderid except one (min returnproductid)
DELETE R FROM returnproducts R
WHERE returnproductid<> (SELECT MIN(returnproductid) FROM returnproducts WHERE productID=R.productID and orderID=R.orderID)
DELETE R FROM returnproducts R
WHERE returnproductid<> (SELECT MIN(returnproductid) FROM returnproducts WHERE productID=R.productID and orderID=R.orderID)
ASKER
thanks
from tablename
group by productid, orderid
having count(*) > 1