Retrieving Info from Smallest Record 1

[ISPrincess]

You have been such GREAT help to me -

Again, I am not sure how to find the answer to this in the forum but here is my problem, simply stated:

I have Location Records such as:

Location = A
NewQty = 100
OldQty = 95
Diff = 5

For this location in another Table I have multiple Pallets, such as:

Pallet = 1
Qty = 25

Pallet = 2
Qty = 25

Pallet = 3
Qty = 20

Pallet = 4
Qty = 25

In the most basic sense I need to be able to retieve the Pallet number with the least quantity (3) in order to increase that pallets quantity by the difference (5).

I have done this on a one by one basis but would like to be able to handle this all in one fell swoop for many records.

If you want to take a look at my existing but not working query - it follows:

INSERT LPHa
Select 
	TransCode as TransStatus, 
	'Qty Increase' as TransMessage,
	PalletID,
	Quantity,
	Location
	From LPHp LPH
	Join Inv INV
	On INV.RFLocation = LPH.CurrentLocation
	Where INV.TransCode = '50001'
	And LPH.PalletID IN
	(Select top 1
	LPH2.LicensePlateno
	From LPHp LPH2
	where LPH2.Location = INV.RFLocation
	Order by Quantity, LPH2.PalletID)

Thank you so much for any help (again...) PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[ISPrincess]

I must take that back - that query works (shown above) but takes 4+ minutes for update of 3200+ records.

So it must be a baaaddddd query. PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[tlbroadbent]

I'm confused by the sub-query. You are selecting LicensePlateno but comparing it to PalletID. That seems wrong to me.

And LPH.PalletID IN
(Select top 1
LPH2.LicensePlateno
From LPHp LPH2
where LPH2.Location = INV.RFLocation
Order by Quantity, LPH2.PalletID)

Wouldn't the following work better or am I misunderstanding something?

And LPH.PalletID =
(Select top 1
LPH2.PalletID
From LPHp LPH2
where LPH2.Location = INV.RFLocation
Order by Quantity, LPH2.PalletID)

Make sure the LPHp table is indexed on Location. Are CurrentLocation and Location the same thing? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[ISPrincess]

So very sorry, you are correct. I sometimes change the terms in my posts from what it is in the query to enhance understanding and I missed 'licenseplateno' s/b PalletID

Thanks... PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[ISPrincess]

Terry,

I still have this problem - do you have any more thoughts on this subject?

Patty PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips

 

[Vancouverite]

INSERT LPHa
Select
TransCode as TransStatus,
'Qty Increase' as TransMessage,
PalletID,
Quantity,
Location
From LPHp LPH
Join Inv INV
On INV.RFLocation = LPH.CurrentLocation
Where INV.TransCode = '50001'
And LPH.PalletID IN
(Select top 1
LPH2.PalletID
From LPHp LPH2
where LPH2.Location = INV.RFLocation
Order by Quantity, LPH2.PalletID)

(1) You do have an index by Quantity and PalletId, yes? Or preferably by Location, Quantity, and PalletID (in that order). If not, create one.

(2) If you have an index like this, try using a temp table to minimize the data manipulated. This assumes (from your previous comment about 4+ min for 3200 recs) that you are modifying or identifying multiple records as a subset of even more records. Actual temp table efficiency dependent on (a) # of recs in the source table, and (b) efficiency of indexes in source table compared to the data being manipulated. For example, if you have 800,000 recs in the source table, but will only be manipulating 3200, you frequently will have a faster response if you extract all of the identifying info from the source table into a temp table.

 

[ISPrincess]

Van - thank you - I set up a new index in the exact order of the sql statement and it worked. I guess that was pretty obvious to every one but me.

Thank you so much for your time! PH
I was walking home one night and a guy hammering on a roof called me a paranoid little weirdo.
In morse code.
-Emo Phillips