Solved

Select only one matching record from each category

Posted on 2009-04-12
7
792 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
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 41

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 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