Ron Kidd
asked on
Remove all duplicates except the first in a SQL data set
Hello
I have a SQL Script that returns a set of Data, I need to remove the duplicates in a Certain column.
When there is a Duplicate value in the MinQty Column I need to remove all the Duplicate values and only Keep the FIRST of the duplicate records.
Following is a Screenshot of some Dummy Data.
Here is what Results I Need.
Many thanks
I have a SQL Script that returns a set of Data, I need to remove the duplicates in a Certain column.
When there is a Duplicate value in the MinQty Column I need to remove all the Duplicate values and only Keep the FIRST of the duplicate records.
Following is a Screenshot of some Dummy Data.
Here is what Results I Need.
Many thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
apparently you didn't try mine (which was complete)
I don't object to the closing request. I just want to make sure the solution is correct.
You said you wanted the "first" of duplicates kept.
Do you want the minimum "Amount" value when there are duplicate "MinQty" values?
Or do you want the lowest-numbered "Code" value?
As written, the code gives you the smallest Amount value, but you may want the lowest Code value instead.
You said you wanted the "first" of duplicates kept.
Do you want the minimum "Amount" value when there are duplicate "MinQty" values?
Or do you want the lowest-numbered "Code" value?
As written, the code gives you the smallest Amount value, but you may want the lowest Code value instead.
ASKER
I see what happened now - When I make the sample data in Excel I dragged the code and it incremented it by 1 each row. :(
(The code was supposed to be the same for each record)
I Need the Smallest Amount PER Code and Min Qty.
Sorry for the Confusion
Thanks Everyone
(The code was supposed to be the same for each record)
I Need the Smallest Amount PER Code and Min Qty.
Sorry for the Confusion
Thanks Everyone
ASKER
Had to Edit to work correctly
Some observations:
If the edits arise from information not made available to the expert, should the expert be graded down?
or denied Accepted Answer?
Having a need to edit the proposed solution isn't reason enough to downgrade the result particularly as it is the technique employing row_number() and "rn=1" that is the essence of that answer.
Is that edit "substantial"?
If the edits arise from information not made available to the expert, should the expert be graded down?
or denied Accepted Answer?
Having a need to edit the proposed solution isn't reason enough to downgrade the result particularly as it is the technique employing row_number() and "rn=1" that is the essence of that answer.
Is that edit "substantial"?
with cte as
(select code,amount,minqty,ROW_NUMBER() OVER(ORDER BY code) rn from tab1)
select code,amount,minqty
from cte
where rn=1
with cte as
(select code,amount,minqty,ROW_NUMBER() OVER(PARTITION BY MINQTY ORDER BY AMOUNT) rn from tab1)
select code,amount,minqty
from cte
where rn=1
At least part of that change is due to the mistake in the supplied data (PARTITION BY MINQTY) isn't it?
Open in new window