asked on

Are you using SQL Server 2005 or 2008?

look at this

use adventureworks
go
create table img
(
	ImgName varchar(20),
	ImgCategory varchar(10)
)
 
insert into img
SELECT 'IMG1.jpg',     'Cat1' UNION ALL
SELECT 'IMG2.jpg',     'Cat1' UNION ALL
SELECT 'IMG3.jpg',     'Cat1' UNION ALL
SELECT 'IMG1.jpg',     'Cat2' UNION ALL
SELECT 'IMG2.jpg',     'Cat2' 
 
 
SELECT max(imgname),imgcategory from img group by imgcategory

Open in new window

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RiteshShah

or you can use this one as well for first image

SELECT min(imgname),imgcategory from img group by imgcategory

Adam

Unless you have and index or something to rank the images in each category, the 'first' image is a bit of an ambiguous description.

That said, here's some SQL that will do what you want to do. You can change the 'min' to 'max' to change which image is selected from each category. If you select the image name carefully, you should be able to control which is selected

select min(ImgName), ImgCategory from dbo.IMGS
group by ImgCategory

Sharath S

You can try the MIN or MAX as suggested by other experts here. Or you can also try like this if you are interested in TOP 1 value irrespective of whether it its MIN or MAX value for that Category.

select * from IMGS t1 
 where ImgName in (select top 1 ImgName from IMGS t2 where t1.ImgCategory = t2.ImgCategory)

Open in new window

MotoCrazy

ASKER

Thank you all for the quick replies!

acperkins, it is 2005 and you nailed it perfectly!

SELECT ImgCategory, MIN(ImgName) ImgName
FROM dbo.pictures
GROUP BY Category

My DataSet is now exactly how I wanted it. Thank you!
Eric

Select only one matching record from each category