Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2010-09-23
8
Medium Priority
?
647 Views
Last Modified: 2012-05-10
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
Comment
Question by:dgloveruk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33746204
Do you have an identity column in the table?
0
 
LVL 7

Expert Comment

by:waltersnowslinarnold
ID: 33746224
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33746354
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 33746370
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
 
LVL 4

Expert Comment

by:parthmalhan
ID: 33751512
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 Comment

by:dgloveruk
ID: 33751931
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
 
LVL 2

Accepted Solution

by:
willz123 earned 2000 total points
ID: 33754097
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
 

Author Closing Comment

by:dgloveruk
ID: 33873774
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question