Post reply

Stuck on a tricky query ... please help?

  • 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!

  • 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

  • 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
    


    

                    
    

        
    
    

    • 









                                                                        

  • 

            
    
        
    
        
        
    
            
    
                February 25, 2004 at 7:55 am            
    

            
            
            
    
                
                    #495560                
            
    

            
            
                                                            
        
    

        
                

        
                        

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

                    
    

        
    
    

    • 









                                    









	

		
Viewing 4 posts - 1 through 3 (of 3 total)

	


	

			








	

        
You must be logged in to reply to this topic. Login to reply