Create a table containing the 4 most recent items

[Katie6]

Hi there,

I have a table containing News items grouped into different categories, like so:

[b]Category      Story                              Date[/b]
----------------------------------------------------------
Sport         Team wins match                    14Oct2007 
Sport         Team does not win match            15Oct2007 
Sport         Football results                   16Oct2007 
Sport         Hockey player falls over           01Nov2007 
Sport         Hooligans spoil match              12Sep2007 
Buiness       Bank collapses                     14Oct2007 
Buiness       Company boss gets the sack         24Oct2007 
Buiness       FTSE 100                           29Oct2007 
Buiness       Manager found under the table      30Oct2007 
Buiness       Tramp inherits company             19Nov2007 
Politics      Prime Minister trips over          15Oct2007 
Politics      President visits North Pole        18Oct2007

From this table, I would like to create another table containing the 4 most recent stories in each category, where story1 is the most recent. It would look like this:

[b]Category    Story1             Story2         .  Story3          Story4 [/b]
---------------------------------------------------------
Sport       Hockey player...   Football results  Team does...   Team wins...
Buiness     Tramp inherits..   Manager found...  FTSE 100  .    Company b...
Politics    President visits.. Prime Minister..

Is there a clever way to do this (or even a not-so-clever way!)?


Many thanks,

Katie

 

[r937]

actually, the not-so-clever way is to try to shoehorn all 4 stories for each category into one line

if you actually have to present them that way, do it with your application language

here's the query --

select Category
     , Story
     , `Date`
  from daTable as T
 where 4 >
       ( select count(*)
           from daTable
          where Category = T.Category
          where `Date` > T.`Date` )

r937.com | rudy.ca