One Filename, One Group, One Question to unite

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

One Filename, One Group, One Question to unite

Author

Topic

BlackIce662
Starting Member

11 Posts

Posted - 2010-08-05 : 06:48:15

The situation: I want to display a landing gallery page kind of like on facebook where you have a image from a specific group
above a hyperlink that will take you to that groups images. So basically one group could have 100 images. For my landing page I just want one filename(Preferably the latest record) per group. So I need a SQL statement that will return a FileName and the group it's from. Eg. If there are 5 groups I want 5 filenames with the group it's from with no other filenames from a similar group. So if there's a 10 images for the wedding group I just want one image from that group.

This is what I tried: SELECT ImgGroup, [FileName] FROM Gallery
WHERE ImgGroup In (SELECT DISTINCT [ImgGroup] FROM [eatingout].[dbo].[Gallery] WHERE MemberID = 2735)

My table:
USE [eatingout]
GO

/****** Object: Table [dbo].[Gallery] Script Date: 08/05/2010 10:16:59 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Gallery]') AND type in (N'U'))
DROP TABLE [dbo].[Gallery]
GO

USE [eatingout]
GO

/****** Object: Table [dbo].[Gallery] Script Date: 08/05/2010 10:16:59 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Gallery](
[GalleryID] [int] IDENTITY(1,1) NOT NULL,
[MemberID] [int] NOT NULL,
[ImgGroup] [varchar](50) NULL,
[Descr] [varchar](50) NULL,
[FileName] [varchar](50) NULL,
[Width] [varchar](50) NULL,
[Height] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Greatness... Live It!!

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 07:01:37

[code]
select * from(
select *,row_number()over(partition by ImgGroup order by galleryid desc)as rid from Gallery
)T where rid=1
[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

parody
Posting Yak Master

111 Posts

Posted - 2010-08-05 : 07:01:56

Got some sample data too (in the form of an insert statement)

BlackIce662
Starting Member

11 Posts

Posted - 2010-08-05 : 07:13:41

Here are some inserts.
If you put this in what I should get in is
the one filename for the wedding imgGroup and one for the Menu group as well as ImgGroup itself

INSERT INTO [eatingout].[dbo].[Gallery]
([MemberID]
,[ImgGroup]
,[Descr]
,[FileName]
,[Width]
,[Height])
VALUES
(<2735, int,>
,<Wedding, varchar(50),>
,<Happy Couple 1, varchar(50),>
,<2735-6219.jpg, varchar(50),>
,<700, varchar(50),>
,<600, varchar(50),>)
GO

INSERT INTO [eatingout].[dbo].[Gallery]
([MemberID]
,[ImgGroup]
,[Descr]
,[FileName]
,[Width]
,[Height])
VALUES
(<2735, int,>
,<Wedding, varchar(50),>
,<Happy Couple 2, varchar(50),>
,<2735-6220.jpg, varchar(50),>
,<700, varchar(50),>
,<600, varchar(50),>)
GO

INSERT INTO [eatingout].[dbo].[Gallery]
([MemberID]
,[ImgGroup]
,[Descr]
,[FileName]
,[Width]
,[Height])
VALUES
(<2735, int,>
,<Menu, varchar(50),>
,<Bread, varchar(50),>
,<2735-6219.jpg, varchar(50),>
,<700, varchar(50),>
,<600, varchar(50),>)
GO

Greatness... Live It!!

BlackIce662
Starting Member

11 Posts

Posted - 2010-08-05 : 07:15:50

It doesn't really matter which image but the most recent entry would be great the important thing is I just need the FileName and it's group

Greatness... Live It!!

BlackIce662
Starting Member

11 Posts

Posted - 2010-08-05 : 07:33:21

F@@# me thank you you Idera pardon my French but I've been struggling whole day with that and your solution works. Appreciate it.

Greatness... Live It!!

Sachin.Nand

2937 Posts

Posted - 2010-08-05 : 07:38:54

quote:
Originally posted by BlackIce662

F@@# me thank you you Idera pardon my French but I've been struggling whole day with that and your solution works. Appreciate it.

Greatness... Live It!!

Welcome

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources