0

I have a table with these columns: stockNumber, storageID, lastPutDate, qtyOnHand
Each stockNumber can have multiple storageID's but only one lastPutDate and qtyOnHand for each storageID.
Here is an example of what the rows might look like:

stockNumber    storageID    lastPutDate    qtyOnHand
-----------    ---------    -----------    ---------
100505         R010101      2012-10-18     150.00
100505         R010102      2012-10-17     75.00
100505         R010103      2012-11-10     97.00
100125         R020101      2012-11-12     200.00
100125         R020204      2012-9-17      500.00
30222          R030101      2012-12-2      63.00

I'm trying to figure out the correct query to end with this result:

stockNumber    storageID    lastPutDate    qtyOnHand
-----------    ---------    -----------    ---------
100505         R010101      2012-10-18     150.00
               R010102      2012-10-17     75.00
               R010103      2012-11-10     97.00
100125         R020101      2012-11-12     200.00
               R020204      2012-9-17      500.00
30222          R030101      2012-12-2      63.00

So far I've been trying something like this:

SELECT stockNumber, storageID, lastPutDate, qtyOnHand FROM WTStockQtys
GROUP BY stockNumber

That's not working though and I'm not even sure if GROUP BY is the right way to do this.
Can anyone help?

2
Contributors
4
Replies
5
Views
4 Years
Discussion Span
Last Post by spowel4
0

I think that the only thing you need is to order the select by stockNumber. Group is used when you want to make an calculation on the values, like SUM or AVG, but it doensn't seem that is what you want.

Do you want to tranform the query result in an object oriented array? Something like the Stock object has an array of Storages? Is that it?

0

My goal is to display the results as a list or table in an html page, with the layout being as I described above.

0

So you'll need a simple query just ordering the results by the stock number. Then when displaying the results you'll have to check if the previous record has the same stock number, if it has don't display it, if it's different, then display it.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.