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