[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Select only one matching record from each category

Posted on 2009-04-12
7
Medium Priority
?
796 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

650 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