holemania
asked on
SQL Query
Not sure how to explain this but will try. So I have some purchase order with 1 or more lines. Not sure how to go about to write this query so that it take the last line with a "PART_ID" and put it into the first line.
In this example, line 2 has the Part_ID. I want to populate the part id into line 1 and remove line 2.
If there are 3 lines, the 3rd line will have the part ID value. It will populate the first line and remove line 2 and 3.
With this example, can this be done via SQL Query?
In this example, line 2 has the Part_ID. I want to populate the part id into line 1 and remove line 2.
If there are 3 lines, the 3rd line will have the part ID value. It will populate the first line and remove line 2 and 3.
With this example, can this be done via SQL Query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this
select a.* , r.cv PURC_ORDER_ID
from yourTable a
cross apply
(
SELECT TOp 1 PURC_ORDER_ID cv
FROM yourtable b
WHERE b.PART_ID = a.PART_ID
order by PART_ID
)r
I think you need to ORDER BY LINE_ID in descending order.
;with cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY PURC_ORDER_ID ORDER BY LINE_ID DESC) rn FROM yourTable)
SELECT * FROM cte WHERE rn = 1
Also, what do you mean by "remove"? Do you want to delete data from table?
Here it is ..
ENjoy!!
select a.* , r.cv PURC_ORDER_ID
from yourTable a
cross apply
(
SELECT TOp 1 PURC_ORDER_ID cv
FROM yourtable b
WHERE b.PART_ID = a.PART_ID
order by LINE_ID DESC
)r
ENjoy!!
Ryan Chong/Sharath,
Neither of yours work because it returns the 'DO NOT RECEIVE THIS LINE' row.
Pawan,
"TOP 1" isn't Oracle.
Neither of yours work because it returns the 'DO NOT RECEIVE THIS LINE' row.
Pawan,
"TOP 1" isn't Oracle.
Try this..
select a.* , r.cv PURC_ORDER_ID
from yourTable a
INNER JOIN
(
SELECT TOP 1 PURC_ORDER_ID cv
FROM yourtable b
WHERE b.PART_ID = a.PART_ID
order by LINE_ID DESC
)x
Apologies, I forgot I ventured out of Oracle and this question is actually SQL Server.
Since I cannot test it, it looks like it will also return the 'DO NOT RECEIVE THIS LINE' row. Can you confirm it doesn't?
Since I cannot test it, it looks like it will also return the 'DO NOT RECEIVE THIS LINE' row. Can you confirm it doesn't?
Sharath
- Author mentioned SQL Server.
- Author mentioned SQL Server.
@holemania,
so have you tried out the suggestions provided above if any of them meet your requirement?
so have you tried out the suggestions provided above if any of them meet your requirement?
@Author - any update on this ? Have you tried suggestion given to you ?
>>Have you tried suggestion given to you ?
Have you confirmed your example doesn't return the 'DO NOT RECEIVE THIS LINE' row. I don;'t have SQL Server to test with but the SQL looks like it will return it.
Have you confirmed your example doesn't return the 'DO NOT RECEIVE THIS LINE' row. I don;'t have SQL Server to test with but the SQL looks like it will return it.
@Author - Check out the below.... Solution by slightwv works. I have also changed mine.
CREATE TABLE tab1(purc_order_id INT, line_no INT, part_no varchar(10), qty INT)
GO
insert into tab1 values(112319,1,null,2700);
insert into tab1 values(112319,2,'ABC123',6);
GO
--SOLUTION BY slightwv (䄆 Netminder)
SELECT * FROM
(
SELECT purc_order_id, line_no, MAX(part_no) OVER(PARTITION BY purc_order_id) part_no, qty
FROM tab1
)r
WHERE line_no=1;
-- My Solution
SELECT purc_order_id, line_no, qty ,part_no FROM
(
SELECT purc_order_id, line_no, ROW_NUMBER() OVER(PARTITION BY purc_order_id ORDER BY part_no DESC) rnk, qty ,part_no
FROM tab1
)r
WHERE rnk=1;
>>Solution by slightwv works.
I know. I tested it long before I ever posted it. That is why I also posted my test case.
>> I have also changed mine.
Seems to pretty much be a copy of what I originally posted. Just a different Window function. I used MAX and you use ROW_NUMBER.
I'm sure I could rewrite mine a few times if we are just looking for different ways to get the same result.
I know. I tested it long before I ever posted it. That is why I also posted my test case.
>> I have also changed mine.
Seems to pretty much be a copy of what I originally posted. Just a different Window function. I used MAX and you use ROW_NUMBER.
I'm sure I could rewrite mine a few times if we are just looking for different ways to get the same result.
<< Ryan Chong/Sharath,
Neither of yours work because it returns the 'DO NOT RECEIVE THIS LINE' row. >>
slightwv, agree with you. Haven't realized that. Thanks for pointing that.
Neither of yours work because it returns the 'DO NOT RECEIVE THIS LINE' row. >>
slightwv, agree with you. Haven't realized that. Thanks for pointing that.
Neither of yours work because it returns the 'DO NOT RECEIVE THIS LINE' row. >>Well, same as many other experts, examples are tested before posted here, or at least working fine in test environment.
Do you simply want the PART_ID from the highest LINE_NO row joined with all remaining columns from the lowest LINE_NO row? Does it matter if the lowest LINE_NO row has a null PART_ID or not? What if the highest LINE_NO row has a null PART_ID?
And are you trying to create a new result set or do you actually want to update the original table?
And are you trying to create a new result set or do you actually want to update the original table?
ASKER
Thank you. that worked.
Open in new window