David Glover
asked on
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
Thanks in advance!
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
Thanks in advance!
Do you have an identity column in the table?
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
Try this query. This should work on SQL Server 2000 also.
Regards
Raj
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
Oh! for "earliest' date use min instead of max
Raj
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
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
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Thanks again!