Overly Complex Simple Query

Overly Complex Simple Query

Post by David Baniste » Sat, 07 Dec 2002 02:37:20



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

 
 
 

Overly Complex Simple Query

Post by BP Margoli » Sat, 07 Dec 2002 03:35:28


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


 
 
 

Overly Complex Simple Query

Post by David Baniste » Sat, 07 Dec 2002 03:43:51


BP,

THANK YOU SOOOOO MUCH
You're a life saver,

David

> 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.



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

 
 
 

1. Simple queries versus complex queries

Hello all,

Microsoft states that " In some cases it is bst to use a group of simple
queries using temp tables to store intermediate results than to use a single
very comple query".

Can anyone give me examples (like SQL for marties) or reallife ones for:

1) simple queries as the best choice
2) complex queries as the best choice

I am very interested about examles for 2) situation.

2. WHY ?

3. Sybase db calls in multi-threaded environment

4. Advice on indexes for complex Select query with very complex Where clause

5. multiple query

6. Many Simple Stored Procedures VS. Few Complex Stored Procedures

7. How do I connect to a standalone database (JDBC2/JNDI)?

8. Simple but complex report

9. simple yet complex join

10. Snapshots Simple vs. Complex

11. Simple Delete Gets Complex..any Ideas