Solved

SQL Query

Posted on 2016-09-28
18
80 Views
Last Modified: 2016-10-18
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?

Example
0
Comment
Question by:holemania
  • 6
  • 5
  • 3
  • +3
18 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41820957
Sorry for the Oracle syntax on the setup but this should be real close to porting easily to SQL Server.

I didn't include all the columns because they weren't 100% necessary for the test.
drop table tab1 purge;
create table tab1(purc_order_id number, line_no number, part_no varchar2(10), qty number);
insert into tab1 values(112319,1,null,2700);
insert into tab1 values(112319,2,'ABC123',6);
commit;

select * from (
select purc_order_id, line_no, max(part_no) over(partition by purc_order_id) part_no, qty
from tab1
)
where line_no=1;

Open in new window

0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41820960
quick try..
with cte as
(
	select *, row_number() over (partition by PURC_ORDER_ID order by PART_ID ) idx
	from yourTable
)
select * from cte
where idx = 1
order by PURC_ORDER_ID, PART_ID

Open in new window

0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41820996
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

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 41821315
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

Open in new window

Also, what do you mean by "remove"? Do you want to delete data from table?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41821425
Here it is ..

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

Open in new window


ENjoy!!
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41821518
Ryan Chong/Sharath,
Neither of yours work because it returns the 'DO NOT RECEIVE THIS LINE' row.

Pawan,
"TOP 1" isn't Oracle.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41821559
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

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41821563
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?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41821584
Sharath
 - Author mentioned SQL Server.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41823060
@holemania,

so have you tried out the suggestions provided above if any of them meet your requirement?
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41824667
@Author - any update on this ? Have you tried suggestion given to you ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41825037
>>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.
0
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
ID: 41825044
@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;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41825728
>>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.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 41825846
<< 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.
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41825949
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.
0
 
LVL 27

Expert Comment

by:tliotta
ID: 41832969
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?
0
 

Author Closing Comment

by:holemania
ID: 41848971
Thank you.  that worked.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now