Select top records but limit query to max from each category
Posted on 2013-01-13
I didn't any help from my last question so I thought I deleted it and rewrote it.
How do I use cfquery to select a total of 21 records from an sql database of thousands of records but limit the query to a maximum of no more than x records (xamt) returned from each category.
xmt=Database tinyint and so is defineable of how many records I want each category to display.
xamtfor sports = 1
xamtfor politics = 2
xamtfor national = 3
As an example, in this case there may be up to 3 records from NATIONAL NEWS, 1 records from SPORTS, 2 records from POLITICS, etc. Display no more than 'xamt' amount for each record.
We assume there are always enough records (ID=Primary Key) and enough categories (CATID [INT] ).
<!--- I know this query doesn't work for what I want but it should get you started --->
<cfquery name="NewsInfo" datasource="ANEWS">
select top 21 *
where online = 1
and catid = ???
and xamt = '#url.x#'
order by id desc
I'm a novice using CF8 standard and sql 2000.