Solved

Select only one matching record from each category

Posted on 2009-04-12
7
787 Views
Last Modified: 2012-05-06
Been working with SQL for about a week now, and n00b needs help! =P

I have a table with tons of images, and I am trying to create an ASPX (VB.Net) page that will display only the first image from each category. From here, the user can click on that image to be taken to a "category.aspx" page with all images in that category. Ie...

dbo.IMGS Table Sample:
ImgName    ImgCategory
IMG1.jpg     Cat1
IMG2.jpg     Cat1
IMG3.jpg     Cat1
IMG1.jpg     Cat2
IMG2.jpg     Cat2

How would I retrieve *ONLY* the two "IMG1.jpg from Cat1" and "IMG1.jpg from Cat2" from SQL?

SELECT TOP 1 *
FROM dbo.IMGS
WHERE ImgCategory *is different?*

Appreciate any help.
Eric
0
Comment
Question by:MotoCrazy
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24126337
Are you using SQL Server 2005 or 2008?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24126355
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

0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 24126356
This should work with any version of SQL Server:

Select IMGCategory, MIN(ImgName) IMGName
FROM IMGS
Group By IMGCategory
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24126357
or you can use this one as well for first image


SELECT min(imgname),imgcategory from img group by imgcategory
0
 
LVL 12

Expert Comment

by:Cyber-spy
ID: 24126420
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
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24126816
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

0
 
LVL 8

Author Closing Comment

by:MotoCrazy
ID: 31569342
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
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Thoughout my experience working on eCommerce web applications I have seen applications succumbing to increased user demand and throughput. With increased loads the response times started to spike, which leads to user frustration and lost sales. I ha…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now