• Status: Solved
• Priority: Medium
• Security: Public
• Views: 651

# Group by with a min() and same row detail..

Given the following two tables :

Table A
StockID      Item
1      Desks
2      Chairs
3      Tables
4      Plants
5      Lights

Table B
StockID      Date      Qty
4      01/01/2010      2
4      05/02/2010      5
4      06/02/2010      1
3      06/02/2010      5
3      14/03/2010      3

What select query would join and select earliest date value only and corresponding quantity. :
e.g :

StockID      Item      Date      Qty
4      Plants      01/01/2010      2
3      Tables      06/02/2010      5

0
dgloveruk
1 Solution

Commented:
Do you have an identity column in the table?
0

Commented:
Use the following code sample..,
``````SELECT a.StockID, a.Item, MIN(b.Date), b.Qty FROM TableA a
INNER JOIN TableB b ON b.StockID = a.StockID
GROUP BY a.StockID, a.Item, b.Qty
``````
0

Software EngineerCommented:
Try this query. This should work on SQL Server 2000 also.

Regards
Raj
``````SELECT a.StockID, a.Item, b.Date, b.Qty
FROM tableB B
INNER JOIN tableA A
on a.StockID = b.StockID
INNER JOIN
(
select StockID, max(date) as Date
from tableB
group by StockID
) C on c.StockID = B.StockID and c.Date = B.Date
``````
0

Software EngineerCommented:
Oh! for "earliest' date use min instead of max

Raj
``````SELECT a.StockID, a.Item, b.Date, b.Qty
FROM #tableB B
INNER JOIN #tableA A
on a.StockID = b.StockID
INNER JOIN
(
select StockID, min(date) as Date
from #tableB
group by StockID
) C on c.StockID = B.StockID and c.Date = B.Date
``````
0

Commented:
Try the following code:

SELECT a.StockID, a.Item, b.Date, b.Qty
FROM t2 B
INNER JOIN t1 A
on a.StockID = b.StockID
INNER JOIN
(
select StockID, min(date) as Date
from t2
group by StockID
) C on c.StockID = B.StockID and c.Date = B.Date

this will give you the records for minimum date for every stockid
0

Author Commented:
Sorry I should have made clearer, this is SQL server 2000 as in the tags, but unfortunately I couldn't see a sql 2000 group to post this against.

Unfortunately none of the examples above worked.  My tables are named a and b for simplicity...

BrandonGalderisi:
There is an identity coloumn in table B but it isn't relevant to the solution or any join.  The earliest date does not mean the lowest identifier in this solution either so it lends no function there.

waltersnowslinarnold:
invalid object name dbo.tableA

RajkumarGS:
Invalid object name dbo.tableB

parthmalhan:
Invalid object name dbo.t2

Thanks guys for your suggestions any tweaks suggested?
0

Commented:
Hi

Try this one. Hope it helps and good luck. You should be able to see the concept on how to get the right answer even if its not 100% corrent
``````select A.item, B.date, B.qty from
A inner join B on A.stockID = B.stockID
inner join
(
select stockID, MIN(date) as date
from B
group by stockID
) X on X.stockID = B.stockID and X.date = B.date
``````
0

Author Commented:
Thanks alot, that got me there.  I often get this sort of query where I need a join based on the minimum value of another field in the joining table and its other corresponding values, so this helps alot.
Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.