i have a table which contains property listings
each property listing can either be a "standard" or "premium"
depending on how much the advertiser has paid to display their
advert
this data is stored in the "advert_type" column (advert_type
=1 means advert is premium / advert_type = 0 means advert is
standard)
when displayed the premium listings appear at the top of the
list
currently we handle this simply by "order by advert_type,
date_advert_placed" - nice and simple
however i want to make the premium listings appear twice
(once at the top and once where they would naturally appear in the
list if they were "standard", so I'm thinking I need to do 2
queries and then join them somehow
the important thing is this query gets hit hard and the table
involved is big so I'm looking for efficiency here
im thinking my 2 queries would be something like
<cfquery datasource="db" name="premium_listing_only" >
select *
from tbl_listings
where advert_type = 1
</cfquery>
<cfquery datasource="db" name="all_listings" >
select *
from tbl_listings
where advert_type = 0 or advert_type = 1
</cfquery>
I could outout the first query and then the second, but of
course I need to do some record set paging so I'm really looking
for one query..
All suggestions gratefully received
TIA