Stuck on a tricky query ... please help?

• Scout7

  Old Hand

  Points: 331

  More actions

  February 25, 2004 at 4:22 am

  #109058

  Hi all, I have a table that contains details of news stories associated with specific entities. I have a need to be able to select the single most recent news story per entity, regardless of the individual date for each entity. My table is setup as follows

  CREATE TABLE [dbo].[news] (

   [news_id] [int] IDENTITY (1, 1) NOT NULL ,

   [news_entity_id] [int] NULL ,

   [news_entity_area_id] [int] NULL ,

   [news_date] [datetime] NULL ,

   [news_date_time] [smallint] NULL ,

   [news_private] [int] NULL ,

   [news_private_release_date] [datetime] NULL ,

   [news_private_release_date_time] [smallint] NULL ,

   [news_expiry_date] [datetime] NULL ,

   [news_expiry_date_time] [smallint] NULL ,

   [news_headline] [varchar] (500) COLLATE Latin1_General_CI_AS NULL ,

   [news_item] [ntext] COLLATE Latin1_General_CI_AS NULL ,

   [news_item_archived] [int] NULL ,

   [news_item_deleted] [int] NULL

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  I need to be able to base my select statement on the following fields news_entity_id, news_date & news_date_time. There can any number of records per entity but I must only return the top 20 news stories from the table based on date and time and the same entity cannot appear more than once.

  If anyone can help me I would appreciate it, also let me know if I have not made myself clear as I am getting quite confused with it already!

• noeld

  SSC Guru

  Points: 96590

  More actions

  February 25, 2004 at 7:32 am

  #495548

  Select *

  from

    news N

    join

    (

   Select  TOP 20 news_entity_id,  Max (

      Convert(char(8),news_date,112)

      +

      Right('000'+ Cast(news_date_time as varchar(4)),4)

     &nbsp as MaxDateTime

   from news

   group by  news_entity_id

   order by  2 DESC

  &nbsp NM on N.news_entity_id = NM.news_entity_id

   and

    N.news_date = Cast(Left(NM.MaxDateTime,8) as datetime)

   and

   N.news_date_time = Cast(Right(NM.MaxDateTime,4) as int)

  ORDER BY N.news_date DESC , news_date_time

  
  * Noel

• NPeeters

  SSChampion

  Points: 12535

  More actions

  February 25, 2004 at 7:39 am

  #495551

   

  I can imagine you're getting confused. So am I, while reading your post...

   

  If I understand you correctly, you want to get the last 20 stories, all from different news_entity_id's. News_date and news_date_time give the order in which you want the stuff returned...

   

  Anyway, here's a go.

  SELECT TOP 20 n.*

  FROM NEWS n

  WHERE news_id IN (SELECT top 1 t.news_id FROM NEWS t

                    WHERE t.news_entity_id = n.news_entity_id

                    ORDER BY news_date DESC, news_date_time DESC)

  ORDER BY news_date DESC, news_date_time DESC

• Scout7

  Old Hand

  Points: 331

  More actions

  February 25, 2004 at 7:55 am

  #495560

  Many thanks guy's. With your help you have saved my sanity. Problem solved.