We help IT Professionals succeed at work.

SQL Need One Row Per Order Assistance

Lee
Lee asked
on
Medium Priority
28 Views
Last Modified: 2020-02-18
Hello All,

I need to return the first tracking number from a table per order (SOP Number) but I keep getting all the tracking numbers. The lowest DEX_ROW_ID per order is the tracking number I need.

Any help?

This is what I have so far:


SELECT DISTINCT
	SalesLineItems.[SOP Type] ,
       SalesLineItems.[SOP Number] ,
       SalesLineItems.[Actual Ship Date] ,
       SOP10107.Tracking_Number,
	   (SELECT TOP 1 dbo.SOP10107.DEX_ROW_ID) AS ID
  FROM
       SalesLineItems LEFT OUTER JOIN SOP10107
       ON SalesLineItems.[SOP Number] = SOP10107.SOPNUMBE

Open in new window



Results

Order      9877      2020-01-17 00:00:00.000      777xxxxx8696                                   3386856
Order      9877      2020-01-17 00:00:00.000      777xxxxx9420                                   3386857


Thanks,
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

See if this gives you what you want:

SELECT  
	SalesLineItems.[SOP Type] , 
       SalesLineItems.[SOP Number] , 
       SalesLineItems.[Actual Ship Date] , 
       SOP10107.Tracking_Number, 
	   dbo.SOP10107.DEX_ROW_ID AS ID 
( 
SELECT  
	SalesLineItems.[SOP Type] , 
       SalesLineItems.[SOP Number] , 
       SalesLineItems.[Actual Ship Date] , 
       SOP10107.Tracking_Number, 
	   dbo.SOP10107.DEX_ROW_ID AS ID 
		,row_number() over(partition by SalesLineItems.[SOP Number] order by dbo.SOP10107.DEX_ROW_ID  desc) rn 
  FROM 
       SalesLineItems LEFT OUTER JOIN SOP10107 
       ON SalesLineItems.[SOP Number] = SOP10107.SOPNUMBE 
) x 
where rn=1 

Lee

Author

Commented:
Hello slightwv,

I am getting a syntax error for the x on line 18.

Rgs,
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

What database product and version?

Lee

Author

Commented:
MS SQL 2012
CERTIFIED EXPERT

Commented:
I believe your are missing a FROM between lines 6 and 7, or a comma at end of line 6?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>>I believe your are missing a FROM


lol... that would do it!!!  copy/paste error.


SELECT  
	SalesLineItems.[SOP Type] , 
       SalesLineItems.[SOP Number] , 
       SalesLineItems.[Actual Ship Date] , 
       SOP10107.Tracking_Number, 
	   dbo.SOP10107.DEX_ROW_ID AS ID 
from 
( 
SELECT  
	SalesLineItems.[SOP Type] , 
       SalesLineItems.[SOP Number] , 
       SalesLineItems.[Actual Ship Date] , 
       SOP10107.Tracking_Number, 
	   dbo.SOP10107.DEX_ROW_ID AS ID 
		,row_number() over(partition by SalesLineItems.[SOP Number] order by dbo.SOP10107.DEX_ROW_ID  desc) rn 
  FROM 
       SalesLineItems LEFT OUTER JOIN SOP10107 
       ON SalesLineItems.[SOP Number] = SOP10107.SOPNUMBE 
) x 
where rn=1 



CERTIFIED EXPERT

Commented:
:P sometimes those are the worst offenders lol
Lee

Author

Commented:
Ok, past the one error, got this with the updated sql:

Msg 4104, Level 16, State 1, Line 16
The multi-part identifier "SalesLineItems.SOP Type" could not be bound.
Msg 4104, Level 16, State 1, Line 17
The multi-part identifier "SalesLineItems.SOP Number" could not be bound.
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier "SalesLineItems.Actual Ship Date" could not be bound.
Msg 4104, Level 16, State 1, Line 19
The multi-part identifier "SOP10107.Tracking_Number" could not be bound.
Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "dbo.SOP10107.DEX_ROW_ID" could not be bound.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

One more try removing more copy/paste issues.

SELECT  
	[SOP Type] , 
       [SOP Number] , 
       [Actual Ship Date] , 
       Tracking_Number, 
	   ID 
from 
( 
SELECT  
	SalesLineItems.[SOP Type] , 
       SalesLineItems.[SOP Number] , 
       SalesLineItems.[Actual Ship Date] , 
       SOP10107.Tracking_Number, 
	   dbo.SOP10107.DEX_ROW_ID AS ID 
		,row_number() over(partition by SalesLineItems.[SOP Number] order by dbo.SOP10107.DEX_ROW_ID  desc) rn 
  FROM 
       SalesLineItems LEFT OUTER JOIN SOP10107 
       ON SalesLineItems.[SOP Number] = SOP10107.SOPNUMBE 
) x 
where rn=1 


Lee

Author

Commented:
That gave me some results.

It gave me the line with the dex_row_id of 3386857 where I need lower number, 3386856. I am not 100% sure what you have done to get the row to kick back so far but thanks!
CERTIFIED EXPERT

Commented:
in lines 1 to 4 replace SalesLineItems with X
  (x.[SOP NUMBER])  etc.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> It gave me the line with the dex_row_id of 3386857 where I need lower number, 3386856.


Change desc to asc:


,row_number() over(partition by SalesLineItems.[SOP Number] order by dbo.SOP10107.DEX_ROW_ID asc) rn


>> I am not 100% sure what you have done to get the row to kick back so far but thanks!


The magic is in the ROW_NUMBER window clause...  It is a cool way to do "grouping and ordering" inline.

CERTIFIED EXPERT

Commented:
ah line 15 change DESC to ASC  
this morning Slightwv must have run out of coffee.
Lee

Author

Commented:
Bingo!

Thanks you two.
CERTIFIED EXPERT

Commented:
thank you but points should really go to Sligthwv, i was just an spectator with too much coffee in my system.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

I'm cool with sharing the points for my Editor!

Lee

Author

Commented:
Thanks Sligthwv for extra bit of info, I am going to have to learn how to use that one.

Arana - sure, does doing the undo for marking correct that?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.