August 5, 2010 at 2:31 am
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
August 5, 2010 at 7:11 am
see if this will work for you:
select ImgGroup, [filename]
from Gallery G
JOIN (select ImgGroup, max(GalleryID) as ID from Gallery group by ImgGroup) Link
ON G.GalleryID = Link.ID
The GalleryID is identity in the table and is unique for each row, so the max(ID) would be the most recent image. But it might be nice to have a datetime column in there as the time the image was added. That way you could age off old ones as well.
The probability of survival is inversely proportional to the angle of arrival.
August 5, 2010 at 7:27 am
Another solution using Row_Number():
;
WITH MyCTEName
AS ( SELECT ImgGroup ,
[FileName] ,
ROW_NUMBER() OVER ( PARTITION BY ImgGroup ORDER BY GalleryID) rownum
FROM Gallery
)
SELECT ImgGroup ,
[FileName]
FROM MyCTEName
WHERE rownum = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply