Select issue - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Select issue

Author

Topic

Corobori
Posting Yak Master

105 Posts

Posted - 2014-01-22 : 10:14:54

Here is my issue: I have got a table where are stored images for an estate.

So far I created a view (see below) retrieving the 1st image for the estate

Now I need to alter thie view in order to retrieve the 1st image unless an image has been preselected (Prf_UseThisOne=1)

Here is the table


CREATE TABLE [dbo].[tblPropImages](
	[Prf_Id] [int] IDENTITY(1,1) NOT NULL,
	[Prf_Pr_Id] [int] NOT NULL,
	[Prf_Image] [nvarchar](50) NOT NULL,
	[Prf_Position] [int] NOT NULL,
	[Prf_UseThisOne] [int] NOT NULL,
 CONSTRAINT [PK_tblPropImages] PRIMARY KEY CLUSTERED 
(
	[Prf_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblPropImages] ADD  CONSTRAINT [DF_Table_1_Prf_Posicion]  DEFAULT ((0)) FOR [Prf_Position]
GO

ALTER TABLE [dbo].[tblPropImages] ADD  CONSTRAINT [DF_Table_1_Prf_Primero]  DEFAULT ((0)) FOR [Prf_UseThisOne]
GO

Here are the data


INSERT INTO [dbo].[tblPropImages]
           ([Prf_Pr_Id]
           ,[Prf_Image]
           ,[Prf_Position]
           ,[Prf_UseThisOne])
     VALUES
           (1,'img1',5,0)
GO

INSERT INTO [dbo].[tblPropImages]
           ([Prf_Pr_Id]
           ,[Prf_Image]
           ,[Prf_Position]
           ,[Prf_UseThisOne])
     VALUES
           (1,'img2',15,0)
GO
INSERT INTO [dbo].[tblPropImages]
           ([Prf_Pr_Id]
           ,[Prf_Image]
           ,[Prf_Position]
           ,[Prf_UseThisOne])
     VALUES
           (1,'img3',10,0)
GO
INSERT INTO [dbo].[tblPropImages]
           ([Prf_Pr_Id]
           ,[Prf_Image]
           ,[Prf_Position]
           ,[Prf_UseThisOne])
     VALUES
           (2,'img10',5,0)
GO

INSERT INTO [dbo].[tblPropImages]
           ([Prf_Pr_Id]
           ,[Prf_Image]
           ,[Prf_Position]
           ,[Prf_UseThisOne])
     VALUES
           (2,'img20',15,1)
GO
INSERT INTO [dbo].[tblPropImages]
           ([Prf_Pr_Id]
           ,[Prf_Image]
           ,[Prf_Position]
           ,[Prf_UseThisOne])
     VALUES
           (2,'img30',10,0)
GO

Here is the view



CREATE VIEW [dbo].[vwImage]
AS
SELECT        B.Prf_Id, B.Prf_Pr_Id, B.Prf_Image, B.Prf_Position
FROM            dbo.tblPropImages AS B INNER JOIN
                             (SELECT        TOP (100) PERCENT Prf_Pr_Id, MIN(Prf_Position) AS iPosition
                               FROM            dbo.tblPropImages
                               GROUP BY Prf_Pr_Id) AS FO ON FO.Prf_Pr_Id = B.Prf_Pr_Id AND FO.iPosition = B.Prf_Position

GO

This view gives the following result

Prf_Id Prf_Pr_Id Prf_Image Prf_Position
1 1 img1 5
4 2 img10 5

But now I need to alter this view in order to get

Prf_Id Prf_Pr_Id Prf_Image Prf_Position
1 1 img1 5
5 2 img20 15

jean-luc
www.corobori.com

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-01-22 : 10:46:04

[code]
CREATE VIEW dbo.vwImage
AS

WITH ImagePos
AS
(
SELECT Prf_Id, Prf_Pr_Id, Prf_Image, Prf_Position
,ROW_NUMBER() OVER (PARTITION BY Prf_Pr_Id ORDER BY Prf_UseThisOne DESC, Prf_Position) AS rn
FROM dbo.tblPropImages
)
SELECT Prf_Id, Prf_Pr_Id, Prf_Image, Prf_Position
FROM ImagePos
WHERE rn = 1;
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 06:02:54

modify your code as this


CREATE VIEW [dbo].[vwImage]
AS
SELECT        B.Prf_Id, B.Prf_Pr_Id, B.Prf_Image, B.Prf_Position
FROM            dbo.tblPropImages AS B INNER JOIN
                             (SELECT        TOP (100) PERCENT Prf_Pr_Id, 
                              COALESCE(MIN(CASE WHEN Prf_UseThisOne=1 THEN Prf_Position ELSE NULL END),MIN(Prf_Position)) AS iPosition
                               FROM            dbo.tblPropImages
                               GROUP BY Prf_Pr_Id) AS FO ON FO.Prf_Pr_Id = B.Prf_Pr_Id AND FO.iPosition = B.Prf_Position

GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources