David,
The best way to request assistance with an SQL query is by posting table
schemas (CREATE TABLEs), sample data (INSERTs), and the results you want
back using the sample data.
create table Articles
(
Article_Name varchar (15) NOT NULL primary key,
Author_Name varchar (5) NOT NULL,
Publish_Date smalldatetime NOT NULL
)
go
insert into Articles values ('Joe Article 1', 'Joe', 'Jan 1, 2002')
insert into Articles values ('Joe Article 2', 'Joe', 'Feb 1, 2002')
insert into Articles values ('Dick Article 1', 'Dick', 'Feb 1, 2002')
insert into Articles values ('Dick Article 2', 'Dick', 'Mar 1, 2002')
insert into Articles values ('Harry Article 1', 'Harry', 'Mar 1, 2002')
insert into Articles values ('Harry Article 2', 'Harry', 'Apr 1, 2002')
insert into Articles values ('David Article 1', 'David', 'Feb 1, 2002')
insert into Articles values ('David Article 2', 'David', 'Mar 1, 2002')
-- select the most recently published articles from each author
select a1.*
from Articles as a1
where a1.Publish_Date = (select max(a2.Publish_Date)
from Articles as a2
where a2.Author_Name = a1.Author_Name)
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
Quote:> I need to do what I think should be a very simple query. I've got a table
of
> magazine articles with basic info. I'm trying to write a query that will
> return the most recently published articles from 5 distinct authors
whoever
> they may be. For example:
> select top 5 PublishDate, distinct Author, ......
> But you can't do that. If anyone could assist me with this query, I would
> greatly appreciate it.
> Thanks,
> David