Solved

Select only one matching record from each category

Posted on 2009-04-12
7
790 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Use Wufoo, an online form creation tool, to make powerful forms. Learn how to choose which pages of your form are visible to your users based on their inputs. The page rules feature provides you with an opportunity to create if:then statements for y…

828 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