Finding the lowest sum (FIFO) 1

[Greencoffee]

I have different quantities of product in different bins. Ultimatley I need to display the bin with the least amount of product. The quantity of product 'X' in a particular bin will be summed. So it will look like this:

Bin Prod QOH
A1 X 15 <--this is computed in the query using sum()
A2 X 26
A3 X 8
A4 X 124
B1 X 1
B2 X 69
B3 X 37
C1 X 88

Now I need to display Bin B1 because it has 1 unit of product.

I thought I would be able to use MIN(SUM()), but this is not valid. I'm currently using MIN() with a nested select that uses the SUM() function. But does anyone know a better way to do this?

 

[Zathras]

I would just use a temporary work table and be done with it.

 

[Greencoffee]

Thanks for the response but in my case I don't think I can use temp tables. Is there another way?

 

[Zathras]

Why can't you just read through the result set keeping track of which row has the lowest QOH. Then display the data from that row?

 

[JarlH]

How did you get that first result? (i.e. A1, X, 15)
Create a view that does the same SELECT.

Then do something like

SELECT * FROM SUM_VIEW
WHERE QOH = (SELECT MIN(QOH) FROM SUM_VIEW)

 

[Zathras]

Sorry, it was late and I wasn't thinking clearly. You don't need to read the entire result set. Just include an ORDER BY to cause the row you want to appear first. Then just read one row, or as many as should be displayed according to the re-order quantity you want to use. For the data as given, you want to order by the 3rd column:
[tt]
ORDER BY 3
[/tt]

 

[Greencoffee]

Zathras...This may be an obvious question but how do you read just the first row?

 

[Zathras]

I don't know what programming language you are using, so I can't be very specific. Generally, you would create (or open) a result set and then proceed to read all of the data in a loop using .Next or something like it.

Just don't loop and all you get is the first record.

If you are using a utility rather than writing a program, perhaps you can use something like
[tt]
SELECT TOP 1 Bin, Prod, Sum(QOH)
FROM YourTable
GROUP BY Bin, Prod
ORDER BY 3
[/tt]
or some variation of that.

 

[Greencoffee]

Thank you Zathras, for the help I think I'm going in the right direction now.

 

[PHV]

Zathras, are you aware that your answers are somewhat far away from ANSI SQL ;-)

 

[Zathras]

Yes, but then so was the question.

 

[JarlH]

Zathras, what do you mean with the original question wasn't ANSI?

 

[Zathras]

Lighten up, JarlH. I don't know if it was ANSI or not. That was just a joke to answer PHV. Since PHV did not offer any specifics as to in what way my answers (all of them?) are "somewhat far away from ANSI SQL," I responded in the only way I could.

Does it really matter?

For the record, it sounded to me more like a programming problem than an SQL issue. But as long as Greencoffee is "going in the right direction now," what difference does it make?

Sheeeesh!