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

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!
0
dgloveruk
Asked:
dgloveruk
1 Solution
 
BrandonGalderisiCommented:
Do you have an identity column in the table?
0
 
waltersnowslinarnoldCommented:
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

Open in new window

0
 
Rajkumar GsSoftware 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

Open in new window

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rajkumar GsSoftware 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

Open in new window

0
 
parthmalhanCommented:
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
 
dgloverukAuthor 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
 
willz123Commented:
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

Open in new window

0
 
dgloverukAuthor 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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now