Link to home
Start Free TrialLog in
Avatar of Lee
LeeFlag for United States of America

asked on

SQL Need One Row Per Order Assistance

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,
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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 

Open in new window

Avatar of Lee

ASKER

Hello slightwv,

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

Rgs,

What database product and version?

Avatar of Lee

ASKER

MS SQL 2012
I believe your are missing a FROM between lines 6 and 7, or a comma at end of line 6?

>>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 

Open in new window



:P sometimes those are the worst offenders lol
Avatar of Lee

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lee

ASKER

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!
in lines 1 to 4 replace SalesLineItems with X
  (x.[SOP NUMBER])  etc.

>> 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.

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

ASKER

Bingo!

Thanks you two.
thank you but points should really go to Sligthwv, i was just an spectator with too much coffee in my system.

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

Avatar of Lee

ASKER

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?