Select TOP1 from results after multiple inner join

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select TOP1 from results after multiple inner join

Author

Topic

madv
Starting Member

4 Posts

Posted - 2007-05-16 : 11:29:51

I'm having some issues understanding some of the examples I've seen on this forum that relate to my subject. I'm actually trying to understand how it works; I can't seem to understand the logic.

Here's what's going on:
I have a database the houses various tables that provide the necessary information to compile a packing slip for an order. Currently I return results for orders that are new and have not yet been printed. The results are made from various inner join syntaxes. Then the results are sorted by (order[ed] by) shipmethod and then by the location where the items are.

What I'd like to implement is: where multiple items in one order just return the top1 of that order. (And yes I know that multiple items have to be placed into the packing slip, but I just really need an ID from this whole query, that ID then gets used to query for the items on that order by another stored procedure from the program I'm writing.) I have provided examples of the results I'm getting and examples of what I'd like to get. Also I've included the select query. Thank you very much for your help. I really want to understand this.

Here's the query

	SELECT
	a.OrderNumber,
	a.MarketName,
	a.MarketOrderID,
	a.Status,
	a.Printed,
	a.MarketShipMethod,
	a.ShipMethod,
	a.MarketShipMethod,
	a.ShipMethod,
	a.CustomerInfoID,
	a.ID,
	a.OrderDate,

	b.Email,
	b.BuyerName,
	b.ShipToName,
	b.Address1,
	b.Address2,
	b.City,
	b.State,
	b.PostalCode,
	b.Country,
	b.ShippingNote,

	e.WarehouseLocator,

			CASE 
			When a.MarketShipMethod = 'standard'
				THEN 'Standard'
			When a.MarketShipMethod = 'uspsmm'
				THEN 'Standard'
			When a.MarketShipMethod = 'Media Mail'
				THEN 'Standard'
			WHEN a.MarketShipMethod = '3 - 14 business days'
				THEN 'Standard'
			WHEN a.MarketShipMethod = '3 - 5 business days'
				THEN 'Standard'

			When a.MarketShipMethod = 'MRS'
				THEN 'Via Alibris'

			WHEN a.MarketShipMethod = ''
				THEN 'International'

			When a.MarketShipMethod = 'expedited'
				THEN 'Expedited'
			ELSE a.MarketShipMethod
				END AS FinalShipMethod,

		--The following prioritizes the shipping based on a predetermined number
			CASE 
			When a.MarketShipMethod = 'standard'
				THEN '4'
			When a.MarketShipMethod = 'uspsmm'
				THEN '4'
			When a.MarketShipMethod = 'Media Mail'
				THEN '4'
			WHEN a.MarketShipMethod = '3 - 14 business days'
				THEN '4'
			WHEN a.MarketShipMethod = '3 - 5 business days'
				THEN '4'

			When a.MarketShipMethod = 'MRS'
				THEN '3'

			WHEN a.MarketShipMethod = ''
				THEN '2'

			When a.MarketShipMethod = 'expedited'
				THEN '1'
			ELSE a.MarketShipMethod
				END AS ShipMethodNumber

	From [Order] a 
    Inner Join CustomerInfo b On a.CustomerInfoId = b.[ID]
    Inner Join OrderItem c on a.[ID] = c.OrderID
	Inner Join OrderItemAllocation d on c.[ID] = d.OrderID
	Inner Join Locator e on d.LocatorID = e.[ID]

	Where
	Status = '1' and Printed = '0'
		 
Group by
		a.OrderNumber, --1
		a.MarketName, --2
		a.Status, --3
		a.Printed, --4
		a.MarketShipMethod, --5
		a.MarketOrderID, --6
		a.ShipMethod, --7
		a.CustomerInfoId, --8
		a.ID, --9
		a.OrderDate, --10
		b.Email, --11
		b.BuyerName, --12
		b.ShipToName, --13
		b.Address1, --14
		b.Address2, --15
		b.City, --16
		b.State, --17
		b.PostalCode, --18 
		b.Country, --19
		b.ShippingNote, --20
		e.WarehouseLocator --21
ORDER BY ShipMethodNumber, e.WarehouseLocator

The results I am getting:
OrderNumber|ShipToName|WarehouseLocator|ShipMethod|ShipMethodNumber
1017289|william bradford|R R0100103011|Expedited|1
1015516|Jared Mc Cleary|Q Q0100113612|Standard|4
1015516|Jared Mc Cleary|R R0100106731|Standard|4

What I'd like to get:
OrderNumber|ShipToName|WarehouseLocator|ShipMethod|ShipMethodNumber
1017289|william bradford|R R0100103011|Expedited|1
1015516|Jared Mc Cleary|Q Q0100113612|Standard|4

//Above you can see that I don't care about the second WarehouseLocator. Actually I don't care which one I get back. I just need one of those to be a result. What I care about is the order number really. But the results need to be in order by ShipMethodNumber (this gives the order priority when processing) and then those to be ordered by WarehouseLocator (This makes fulfilling the order a snap. No need to travel every where to get items.)

If there's a better method of doing what I want I'd like to learn about it. The ultimate goal is to be able to print our order slips based on shipping priority and the order those by item locations. The problem is the multiple item orders. They return as separate orders when they're part of another. If there is a way to append so called "duplicates" to the end of a row then that just might work with the program I'm writing. But I'd like to learn this anyway. If I'm making no sense please let me know. I'm really interested in getting to know all this.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-16 : 12:55:53

try removing the "e.WarehouseLocator" from the group by list and put a MIN(e.WarehouseLocator) in your SELECT. You can do a MAX() also.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madv
Starting Member

4 Posts

Posted - 2007-05-16 : 14:27:39

I believe I actually attempted that earlier before trying ask for help. Either way, here's the message I get.

"Msg 8127, Level 16, State 1, Line 1
Column "Locator.WarehouseLocator" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-16 : 15:39:02

Use the same MIN or MAX you have in your SELECT query, in your ORDER BY too.

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 15:50:40

[code]SELECT q.OrderNumber,
q.MarketName,
q.MarketOrderID,
q.Status,
q.Printed,
q.MarketShipMethod,
q.ShipMethod,
q.CustomerInfoID,
q.ID,
q.OrderDate,
q.Email,
q.BuyerName,
q.ShipToName,
q.Address1,
q.Address2,
q.City,
q.State,
q.PostalCode,
q.Country,
q.ShippingNote,
q.WarehouseLocator,
q.FinalShipMethod,
q.ShipMethodNumber
FROM (
SELECT a.OrderNumber,
a.MarketName,
a.MarketOrderID,
a.Status,
a.Printed,
a.MarketShipMethod,
a.ShipMethod,
a.CustomerInfoID,
a.ID,
a.OrderDate,
b.Email,
b.BuyerName,
b.ShipToName,
b.Address1,
b.Address2,
b.City,
b.State,
b.PostalCode,
b.Country,
b.ShippingNote,
e.WarehouseLocator,
CASE
When a.MarketShipMethod = 'standard' THEN 'Standard'
When a.MarketShipMethod = 'uspsmm' THEN 'Standard'
When a.MarketShipMethod = 'Media Mail' THEN 'Standard'
WHEN a.MarketShipMethod = '3 - 14 business days' THEN 'Standard'
WHEN a.MarketShipMethod = '3 - 5 business days' THEN 'Standard'
When a.MarketShipMethod = 'MRS' THEN 'Via Alibris'
WHEN a.MarketShipMethod = '' THEN 'International'
When a.MarketShipMethod = 'expedited' THEN 'Expedited'
ELSE a.MarketShipMethod
END AS FinalShipMethod,
CASE
When a.MarketShipMethod = 'standard' THEN '4'
When a.MarketShipMethod = 'uspsmm' THEN '4'
When a.MarketShipMethod = 'Media Mail' THEN '4'
WHEN a.MarketShipMethod = '3 - 14 business days' THEN '4'
WHEN a.MarketShipMethod = '3 - 5 business days' THEN '4'
When a.MarketShipMethod = 'MRS' THEN '3'
WHEN a.MarketShipMethod = '' THEN '2'
When a.MarketShipMethod = 'expedited' THEN '1'
ELSE a.MarketShipMethod
END AS ShipMethodNumber,
ROW_NUMBER() OVER (PARTITION BY a.OrderNumber ORDER BY e.WarehouseLocator DESC) AS RecID
From [Order] AS a
Inner Join CustomerInfo as b On b.[ID] = a.CustomerInfoId
Inner Join OrderItem as c on c.OrderID = a.[ID]
Inner Join OrderItemAllocation as d on d.OrderID = c.[ID]
Inner Join Locator as e on e.[ID] = d.LocatorID
Where Status = '1'
and Printed = '0'
) as q
where q.recid = 1
ORDER BY q.ShipMethodNumber[/code]

Peter Larsson
Helsingborg, Sweden

madv
Starting Member

4 Posts

Posted - 2007-05-16 : 16:46:03

Hahaha I like this one. I actually understand it. It works. It has a small kink though.

It's missing one order. Haha. A single order. When I do a query for orders using a simplified select statement I get 226 results. This simplified statement does not sort in any way. It just pulls orders from the database where it is new and not-printed. When I do it using this great method, I get 225 results back. Unfortunately I don't know which one is missing. Perhaps in the morning after those orders are fulfulled using our previous methods I can work with a smaller number and actually find out what's up.

Other wise, anyone have a clue why this is occuring? It's not the end of the world, but it's kinda funny.

Thanks alot for your help! It works! You understood exactly what I needed. And best of all, I understand why it works!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-16 : 17:00:19

Add table prefix to colums status and printed.

I have no idea why your "simplified" select statement get 226 records and my suggestion returns 225 records.

Peter Larsson
Helsingborg, Sweden

madv
Starting Member

4 Posts

Posted - 2007-05-16 : 18:03:59

Hey Peter,
I added that prefix just as you suggested and the results are the same. Having the prefix on or off really does nothing. This is really weird because the whole statement is logical. There's really no exclusion.

Could a RecID value be given incorrectly to an order where it was supposed to be 1? This is the only way we could have excluded an order.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-17 : 02:32:05

The first thing that come to my mind, is that some order's warehouselocator is null.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources