Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select only one matching record from each category

Posted on 2009-04-12
7
Medium Priority
?
799 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 2000 total points
ID: 24126356
This should work with any version of SQL Server:

Select IMGCategory, MIN(ImgName) IMGName
FROM IMGS
Group By IMGCategory
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

572 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