Avatar of Lee
Lee
Flag 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,
SQL

Avatar of undefined
Last Comment
Lee

8/22/2022 - Mon
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

Lee

ASKER
Hello slightwv,

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

Rgs,
slightwv (䄆 Netminder)

What database product and version?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Lee

ASKER
MS SQL 2012
Arana (G.P.)

I believe your are missing a FROM between lines 6 and 7, or a comma at end of line 6?
slightwv (䄆 Netminder)

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



⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Arana (G.P.)

:P sometimes those are the worst offenders lol
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
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Arana (G.P.)

in lines 1 to 4 replace SalesLineItems with X
  (x.[SOP NUMBER])  etc.
slightwv (䄆 Netminder)

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

Arana (G.P.)

ah line 15 change DESC to ASC  
this morning Slightwv must have run out of coffee.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lee

ASKER
Bingo!

Thanks you two.
Arana (G.P.)

thank you but points should really go to Sligthwv, i was just an spectator with too much coffee in my system.
slightwv (䄆 Netminder)

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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?