General SQL Question

[milner]

Hi,

Say I have a database with two tables, one a list of [boxes], the other a list of the [items] in those boxes.

Each item is number sequentialy ([PositionInBox]).

I want to make a query that will tell me the first and last item in a certain set of boxes (MIN and MAX of [PositionInBox]). (for example, WHERE [boxes].[Type] = 'big')

Could someone please point me in the right direction?

Thanks,

Mike

 

[tlbroadbent]

Assuming the tables are related by a key column, presumably BoxID, the query would look like this.

Select
b.BoxID,
Max(i.PositionInBox) As MaxPosition,
Min(i.PositionInBox) As MinPosition
From Boxes b Inner Join Items i
On b.BoxID=i.BoxID
Where b.Type='big'
Group By BoxID Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[milner]

Thanks!

That worked perfectly for me. I have one other related question:

my [Items] table has another field, [Name]. Can I use the above query to get the [Name] of the first and last item instead of the first and last [PositionInBox]?? (Name is NOT in any specific order)

I'd prefer not to use sub-selects if there is an alternative.

Thanks again!

Mike

 

[tlbroadbent]

I really can't think of a way to get the name without sub-queries.

Select
a.BoxID,
b.PositionInBox As MaxPos,
b.Name As MaxName,
c.PositionInBox As MinPos,
c.Name As MinName
From Boxes a
Inner Join Items b
On a.BoxId=b.BoxID
Inner Join Items c
On a.BoxId=c.BoxID
Where a.Type=a.Type
And b.PositionInBox=
(Select max(PositionInBox)
From Items Where BoxId=a.BoxID)
And c.PositionInBox=
(Select min(PositionInBox)
From Items Where BoxId=a.BoxID) Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin