a complex query

I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)

I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.SELECT TOP 1 IDNews, Country, PublishDate, Title From News Group By Country Order By PublishDate DESC?|||Try this:

select IDNews, Country, PublishDate, Title
from NEWS
where IDNews in
(
select
max(IDnews)
from news n
join
(select
country, max(publishdate) as publishdate
from news
group by country) md
on n.country = md.country
and n.publishdate = md.publishdate
group by n.country
)|||

select
 news.*
from
 news
 inner join
 (select
 country, max(publishdate) as publishdate
 from
 news
 group by
 country
 ) as TMP
 on news.country = TMP.country
 and news.publishdate = TMP.publishdate

sql

50 codes with country names 2 fields

This is my problem: I have a report with 100 records. Every record was assigned a different {country.code}. I was given a hard copy with the list of 45 countries and their respective code (1 represents USA, 2 Mexico, 3 Spain etc). My report doesn't have the name of the country, only the code. Is there something where I say something like: if {country.code} = "3" then "Spain" else...............Do I have to do this for all 45 codes with its country name? That means 45 if-then-else statements.
Is there a quick way of Having my 100 records in my report spell the country name?
Thanks for your help.
JavPut the code / name mapping into a new table and join to it.
If you can't do that then use a select case statement in a formula, e.g.
Select {table.field}
Case 1 : "USA"
Case 2 : "Mexico"
...
Default : "Unknown!"

See the help on "select expression" in the search tab, not the index tab.