Getting top row(s) based on date

From: Rob Meade (robb.meade_at_NO-SPAM.kingswoodweb.net)
Date: 04/12/04 

Date: Mon, 12 Apr 2004 07:58:18 GMT

Hi all,

I have a view which contains details of news items posted, I would very much
like to be able to list the newest post for each of the various news pages
in a summary, so for example if I have 5 different news pages, it would
retrieve the most recent post (based on the date posted) from each of the
news pages (based on the NewsID) and return them in a recordset.

I have tried my best to post the code needed to create the tables/views
below (first time I've tried this)...

Any help would be appreciated

Regards

Rob

PS: I've tried to do the stuff below but I couldn't find an easy way to
script any data for you, I hope that my tables/view are simple enough to
make it obvious etc..

*****************************************************************
NEWS TABLE
*****************************************************************
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblNews]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblNews]
GO

CREATE TABLE [dbo].[tblNews] (
 [NewsID] [int] IDENTITY (1, 1) NOT NULL ,
 [NewsPageID] [int] NOT NULL ,
 [NewsName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [NewsDescription] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [NewsItemsDisplayed] [int] NOT NULL ,
 [NewsItemsDisplayOrder] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
 [NewsDisplayPostedBy] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [NewsDisplayPostedDateTime] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
 [NewsCreatedDate] [datetime] NOT NULL ,
 [NewsCreatedBy] [int] NOT NULL ,
 [NewsLastUpdatedDate] [datetime] NOT NULL ,
 [NewsLastUpdatedBy] [int] NOT NULL
) ON [PRIMARY]
GO

*****************************************************************
NEWS ITEMS TABLE
*****************************************************************

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblNewsItems]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblNewsItems]
GO

CREATE TABLE [dbo].[tblNewsItems] (
 [NewsItemID] [int] IDENTITY (1, 1) NOT NULL ,
 [NewsID] [int] NOT NULL ,
 [NewsItemName] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
 [NewsItemContent] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
 [NewsItemCreatedDate] [datetime] NOT NULL ,
 [NewsItemCreatedBy] [int] NOT NULL ,
 [NewsItemLastUpdatedDate] [datetime] NOT NULL ,
 [NewsItemLastUpdatedBy] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

*****************************************************************
VIEW
*****************************************************************
CREATE VIEW dbo.viewNewsItems
AS
SELECT dbo.tblNewsItems.NewsItemID, dbo.tblNewsItems.NewsID,
dbo.tblNews.NewsPageID, dbo.tblNews.NewsName, dbo.tblNews.NewsDescription,
                      dbo.tblNews.NewsItemsDisplayed,
dbo.tblNews.NewsItemsDisplayOrder, dbo.tblNews.NewsDisplayPostedBy,
                      dbo.tblNews.NewsDisplayPostedDateTime,
dbo.tblNews.NewsCreatedDate, dbo.tblNews.NewsCreatedBy,
dbo.tblNews.NewsLastUpdatedDate,
                      dbo.tblNews.NewsLastUpdatedBy,
dbo.tblNewsItems.NewsItemName, dbo.tblNewsItems.NewsItemContent,
dbo.tblNewsItems.NewsItemCreatedDate,
                      dbo.tblNewsItems.NewsItemCreatedBy,
dbo.tblNewsItems.NewsItemLastUpdatedDate,
dbo.tblNewsItems.NewsItemLastUpdatedBy,
                      dbo.tblPageAssociations.SectionID,
dbo.tblSectionAssociations.WebsiteID,
                      dbo.tblUsers.Forename + ' ' + dbo.tblUsers.Surname AS
NewsItemCreatedByName,
                      tblUsers_1.Forename + ' ' + tblUsers_1.Surname AS
NewsItemUpdatedByName
FROM dbo.tblNews INNER JOIN
                      dbo.tblNewsItems ON dbo.tblNews.NewsID =
dbo.tblNewsItems.NewsID LEFT OUTER JOIN
                      dbo.tblUsers tblUsers_1 ON
dbo.tblNewsItems.NewsItemLastUpdatedBy = tblUsers_1.UserID LEFT OUTER JOIN
                      dbo.tblUsers ON dbo.tblNewsItems.NewsItemCreatedBy =
dbo.tblUsers.UserID LEFT OUTER JOIN
                      dbo.tblSectionAssociations RIGHT OUTER JOIN
                      dbo.tblPageAssociations ON
dbo.tblSectionAssociations.SectionID = dbo.tblPageAssociations.SectionID ON
                      dbo.tblNews.NewsPageID =
dbo.tblPageAssociations.PageID

Relevant Pages

  • Re: "Sorting" assignment
    ... #define elementtype News ... typedef struct { ... int ID; ... Because if you could have written it, why not do the simpler array ...
    (comp.programming)
  • Re: News/Announcements form?
    ... NewsId <int> ... Whenever you create a news item, ... into the UserNews table for every user in the system. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: "Sorting" assignment
    ... Every news contains subject and body text. ... bin B depending on whether it is greater than, ... int quicksortPartition ... int intStoreIndex = intLeft; ...
    (comp.programming)
  • Re: Need help with reordering items
    ... > Here is a solution to the problem that uses a single update query: ... > set nocount on ... > Position int NOT NULL, ... > News wrote: ...
    (microsoft.public.sqlserver.programming)
  • Re: FoxPro 2.6: SELECT statment, Function Call is irgnored, WHY ???
    ... In news: elo40cFEEHA.712@tk2msftngp13.phx.gbl, ... Anders Altberg wrote: ... > get a datetime value back. ... Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP ...
    (microsoft.public.fox.programmer.exchange)
Loading