June 10, 2002 at 8:40 pm
I wish to create a view retrieving the Id (Co_Id) corresponding
to the last comment input for a particular news. The view I created in this script is based upon another view does almost the trick but if there is a comment input at the same time for the same time it won't work. How can I avoid this
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblCliente_Comentario]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblCliente_Comentario]
GO
CREATE TABLE [dbo].[tblCliente_Comentario] (
[Co_Id] [int] NOT NULL ,
[Co_Cl_Id] [int] NULL ,
[Co_No_Id] [int] NULL ,
[Co_Texto] [nvarchar] (800) COLLATE SQL_Latin1_General_CP850_CS_AS NULL ,
[Co_InputFecha] [datetime] NULL ,
[Co_InputUsuario] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CS_AS NULL ,
[Co_UpdateFecha] [datetime] NULL ,
[Co_UpdateUsuario] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CS_AS NULL
) ON [PRIMARY]
GO
insert into tblCliente_Comentario values (2002156001, 2002102001, 2002113002, 'co 1', '2002-05-01','' ,'' ,'' )
insert into tblCliente_Comentario values (2002157001, 2002102001, 2002113002, 'co 2', '2002-06-02','' ,'' ,'' )
insert into tblCliente_Comentario values (2002157002, 2002102001, 2002113002, 'co 3', '2002-06-13','' ,'' ,'' )
insert into tblCliente_Comentario values (2002157003, 2002102001, 2002113002, 'co 4', '2002-06-04','' ,'' ,'' )
insert into tblCliente_Comentario values (2002166001, 2002102001, 2002113002, 'co 5', '2002-04-05','' ,'' ,'' )
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwLastComentForOneNews]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwLastComentForOneNews]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vwLastComentForOneNews
AS
SELECT MAX(Co_InputFecha) AS EXPR1
FROM dbo.tblCliente_Comentario
WHERE (Co_No_Id = 2002113002)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwLastComment]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[vwLastComment]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vwLastComment
AS
SELECT Co_Id, Co_InputFecha
FROM dbo.tblCliente_Comentario
WHERE (Co_InputFecha IN
(SELECT MAX(Co_InputFecha) AS EXPR1
FROM dbo.tblCliente_Comentario
WHERE (Co_No_Id = 2002113002)))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Select * From vwLastComment
Jean-Luc
Jean-Luc
www.corobori.com
June 11, 2002 at 5:58 am
Is there a field taht is unique and even if two records have the same date time it will be incremented or one value is greater than the other? If so use that field as a primary key if possible, if not then say Co_Id is unique for the time consider looking at either the largest or smallest value (MAX or MIN).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 11, 2002 at 11:51 am
quote:
Is there a field taht is unique and even if two records have the same date time it will be incremented or one value is greater than the other?
Yes and no ! There is a unique field key, the primary key, but it is not incremented by one on each Insert
Jean-Luc
Jean-Luc
www.corobori.com
June 11, 2002 at 11:58 am
This may be a silly question, but can't you add another column which is an integer IDENTITY? Then use that?
Could you also use a correlated subquery in your view (not sure if that works), so that you return one or many rows based on the most recently dated and timed row for a chosen client?
Regards
Simon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply