Need help with query and aggregate function

T

Tomas Eklund

I guess this must be really simple, but I just cannot figure it out.

I have a table which lists product images. The images are custom sorted
with the help of a SortOrder field. Now, I need a subquery to extract, for
every ProductID, the image with the lowest SortOrder value. The lowest
SortOrder value is not always 1 (in which case this would have been
trivial). Here is a simplified data structure that illustrates.

CREATE TABLE tblImages (
ImageID INT,
ProductID INT,
SortOrder INT
);

INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (1,1,2);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (2,1,1);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (3,1,4);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (4,3,2);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (5,4,9);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (6,4,8);

With the sample data above the query should return (I don't really need to
return the SortOrder value though, just the ImageID and ProductID fields):

ImageID, ProductID, (SortOrder)
2, 1, 1
4, 3, 2
6, 4, 8

Many thanks!
Tomas Eklund
 
A

Allen Browne

1. Create a query using tblImages.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

3. Drag ProductID into the grid.
Accept Group By in the Total row under this field.

4. Drag ImageID into the grid.
Choose Min in the Total row.

An alternative approach would be to use your products table, with a subquery
to get the image:

SELECT ProductID,
(SELECT Min(ImageID) FROM tblImages
WHERE tblImages.ProductID = tblProduct.ProductID) AS ImageID
FROM tblProduct;
 
T

Tomas Eklund

Allen, thanks for taking your time.

I'm sorry but that query will not work. I do not need to find the image
with the lowest ImageID (that would also have been almost trivial) - I
need to find the image with the *lowest SortOrder* value.

I have gotten this far:

SELECT ProductID, MIN(SortOrder)
FROM tblImages
GROUP BY ProductID;

ProductID, Min(SortOrder)
1, 1
3, 2
4, 8

Which returns the correct rows, but the problem is that it doesn't give me
the ImageID. As soon as I try to add the ImageID field to the query, I run
into trouble:

SELECT ImageID, ProductID, MIN(SortOrder)
FROM tblImages
GROUP BY ProductID;

"you tried to run a query that does not include the specified expression
'ImageID' as part of the aggregate function"

So, I guess I still need some help...

Best regards
Tomas Eklund


----- Original Message -----
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Thursday, November 23, 2006 4:07 PM
Subject: Re: Need help with query and aggregate function

1. Create a query using tblImages.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

3. Drag ProductID into the grid.
Accept Group By in the Total row under this field.

4. Drag ImageID into the grid.
Choose Min in the Total row.

An alternative approach would be to use your products table, with a
subquery
to get the image:

SELECT ProductID,
(SELECT Min(ImageID) FROM tblImages
WHERE tblImages.ProductID = tblProduct.ProductID) AS ImageID
FROM tblProduct;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


----- Original Message -----
From: "Tomas Eklund" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Thursday, November 23, 2006 3:47 PM
Subject: Need help with query and aggregate function

I guess this must be really simple, but I just cannot figure it out.

I have a table which lists product images. The images are custom sorted
with the help of a SortOrder field. Now, I need a subquery to extract, for
every ProductID, the image with the lowest SortOrder value. The lowest
SortOrder value is not always 1 (in which case this would have been
trivial). Here is a simplified data structure that illustrates.

CREATE TABLE tblImages (
ImageID INT,
ProductID INT,
SortOrder INT
);

INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (1,1,2);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (2,1,1);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (3,1,4);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (4,3,2);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (5,4,9);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (6,4,8);

With the sample data above the query should return (I don't really need to
return the SortOrder value though, just the ImageID and ProductID fields):

ImageID, ProductID, (SortOrder)
2, 1, 1
4, 3, 2
6, 4, 8

Many thanks!
Tomas Eklund
 
A

Allen Browne

Sorry: got distracted while answering.

SELECT ProductID,
(SELECT TOP 1 ImageID FROM tblImages
WHERE tblImages.ProductID = tblProduct.ProductID
ORDER BY SortOrder, ImageID) AS ImageID
FROM tblProduct;
 
T

Tomas Eklund

Ah, perfect! So elegant and simple, it's almost embarrasing... ;-)

Great! Many thanks!
Tomas Eklund


P.S. And so my Gmail address got into the public domain. Dang!
How could I miss that?

----- Original Message -----
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Thursday, November 23, 2006 6:29 PM
Subject: Re: Need help with query and aggregate function


Sorry: got distracted while answering.

SELECT ProductID,
(SELECT TOP 1 ImageID FROM tblImages
WHERE tblImages.ProductID = tblProduct.ProductID
ORDER BY SortOrder, ImageID) AS ImageID
FROM tblProduct;

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

----- Original Message -----
From: "Tomas Eklund" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Thursday, November 23, 2006 5:43 PM
Subject: Re: Need help with query and aggregate function

Allen, thanks for taking your time.

I'm sorry but that query will not work. I do not need to find the image
with the lowest ImageID (that would also have been almost trivial) - I
need to find the image with the *lowest SortOrder* value.

I have gotten this far:

SELECT ProductID, MIN(SortOrder)
FROM tblImages
GROUP BY ProductID;

ProductID, Min(SortOrder)
1, 1
3, 2
4, 8

Which returns the correct rows, but the problem is that it doesn't give me
the ImageID. As soon as I try to add the ImageID field to the query, I run
into trouble:

SELECT ImageID, ProductID, MIN(SortOrder)
FROM tblImages
GROUP BY ProductID;

"you tried to run a query that does not include the specified expression
'ImageID' as part of the aggregate function"

So, I guess I still need some help...

Best regards
Tomas Eklund


----- Original Message -----
From: "Allen Browne" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Thursday, November 23, 2006 4:07 PM
Subject: Re: Need help with query and aggregate function

1. Create a query using tblImages.

2. Depress the Total button on the toolbar.
Access adds a Total row to the grid.

3. Drag ProductID into the grid.
Accept Group By in the Total row under this field.

4. Drag ImageID into the grid.
Choose Min in the Total row.

An alternative approach would be to use your products table, with a
subquery
to get the image:

SELECT ProductID,
(SELECT Min(ImageID) FROM tblImages
WHERE tblImages.ProductID = tblProduct.ProductID) AS ImageID
FROM tblProduct;

----- Original Message -----
From: "Tomas Eklund" <[email protected]>
Newsgroups: microsoft.public.access.queries
Sent: Thursday, November 23, 2006 3:47 PM
Subject: Need help with query and aggregate function

I guess this must be really simple, but I just cannot figure it out.

I have a table which lists product images. The images are custom sorted
with the help of a SortOrder field. Now, I need a subquery to extract, for
every ProductID, the image with the lowest SortOrder value. The lowest
SortOrder value is not always 1 (in which case this would have been
trivial). Here is a simplified data structure that illustrates.

CREATE TABLE tblImages (
ImageID INT,
ProductID INT,
SortOrder INT
);

INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (1,1,2);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (2,1,1);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (3,1,4);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (4,3,2);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (5,4,9);
INSERT INTO tblImages (ImageID, ProductID, SortOrder) VALUES (6,4,8);

With the sample data above the query should return (I don't really need to
return the SortOrder value though, just the ImageID and ProductID fields):

ImageID, ProductID, (SortOrder)
2, 1, 1
4, 3, 2
6, 4, 8

Many thanks!
Tomas Eklund
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Concurrency Help 6
Limits of a Switch Function? (Recoding Value into Value Labels) 3
Aggregate function error 2
help with sub select -urgent - please 2
Help with SQL Query 1
Help with SQL insert into 2 one to one relationship tables 1
Updating and Merging with Order Details 3
stored procedure takes too much time to execute 7

Top