Solved

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

Posted on 2010-09-23
645 Views
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
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

LVL 39

Expert Comment

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

LVL 7

Expert Comment

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
0

LVL 23

Expert Comment

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
0

LVL 23

Expert Comment

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
0

LVL 4

Expert Comment

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

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

willz123 earned 500 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
0

Author Closing Comment

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

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
###### Suggested Courses
Course of the Month2 days, 16 hours left to enroll

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

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