Tricky SQL statement

[Shrum]

I need to create a single SQL statement that will do two (or more) queries. I'm pretty sure that what I'm asking for is possible but just have never done it before.

I webmaster a site where I want to display on the homepage:

> the top 3 viewed items (col name='views',int), grouped together
> the top 3 newest items (col name='submission_date',timestamp), grouped together

Therefore, I need them to be grouped as such

rec1 = #1 viewed
rec2 = #2 viewed
rec3 = #3 viewed
rec4 = #1 newest
rec5 = #2 newest
rec6 = #3 newest

What is the syntax for doing this sort of query? Bear in mind that the col names must remain the same and I realize that the query might return the same record(s) twice (if they match both view and newest conditions)

TIA

Sean Shrum

http://consulting.shrum.net

 

[r937]

here ya go, one query:

[tt]select 'viewed'
, itemname
, views
, submission_date
from yourtable
order
by views desc
limit 3
union all
select 'newest'
, itemname
, views
, submission_date
from yourtable
order
by submission_date desc
limit 3[/tt]

if you're not on mysql 4, you can't use UNION, so you'll have to run two queries

rudy

http://r937.com/