Solved

Select top records but limit query to max from each category

Posted on 2013-01-13
27
414 Views
Last Modified: 2013-01-17
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 *
    from mainnews
    where online = 1
    and catid = ???
    and xamt = '#url.x#'
    order by id desc
</cfquery>

I'm a novice using CF8 standard and sql 2000.
0
Comment
Question by:Qsorb
  • 10
  • 7
  • 4
  • +2
27 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 38774621
Wich columns do you need? Are you going to make any COUNT(), SUM() or other function operation?
0
 
LVL 13

Expert Comment

by:PCIIain
ID: 38774674
This is nasty, and will prbably scan the entire table, but I don't see how you get the top 21 only otherwise.
You need another table catlimits with two columns, catid and limit where limit is your xamtfor.


select top 21 from (
select row_number() over (partition by catid order by id desc) as cat_counter, *
from mainnews where online = 1
) as rd
where not exists (select * from catlimits cl where cl.catid = rd.catid)
or exists (select * from catlimits cl where cl.catid = rd.catid and cl.limit >= cat_counter)
order by id desc
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 38774825
is this what you mean?

Declare @xamt int
Select @xamt = xamt from mainnews where online = 1 and catid = ???

select top (@xamt) * NOTE: specify your columns here - do not use * - will improve performance
    from mainnews
    where online = 1
    and catid = ???
    order by id desc
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38775513
ROW_NUMBER() won't work in SQL 2000.

For SQL 2000, I'd suggest SELECTing each category separately, with the appropriate TOP clause, into a temp table, then do the final SELECT from the temp table.
0
 
LVL 13

Expert Comment

by:PCIIain
ID: 38775611
Good point, and best solution.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 38775858
UNLESS I started thinking - does sql 2000 support TOP (@N) ?
I did a quick google and it looks like it does - according to the one result I skimmed through.
But if it doesn't one can use rowcount which I do remember using.

so for your code you would do:
Declare @xamt int
Select @xamt = xamt from mainnews where online = 1 and catid = ???
set rowcount @xamt
select *
    from mainnews
    where online = 1
    and catid = ???
    order by id desc
set rowcount = 0 -- to reset
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38775887
maybe end up with something roughly like this:


select top 21 *
from (
    select top 1 *
    from mainnews
    where online = 1
    and catid = 'sports'
    and xamt = '#url.x#'
    order by id desc
    union all
    select top 2 *
    from mainnews
    where online = 1
    and catid = 'politics'
    and xamt = '#url.x#'
    order by id desc
    union all
    select top 3 *
    from mainnews
    where online = 1
    and catid = 'national'
    and xamt = '#url.x#'
    order by id desc
    ...
) as derived
order by id desc
0
 

Author Comment

by:Qsorb
ID: 38777046
catid = ??? simply means I had no idea what to place there, noted by the three question marks. Anyway, catid is a number from datatype INT.

> do not use * - will improve performance.
Right. Just used that to keep it simple here.

>Are you going to make any COUNT(), SUM() or other function operation?
No.

>select top (@xamt) *
Hmm. Not familiar with @ in the query. Stumped.

Anyway, I'll look at all these, see if I can make any work and get back to you all with your suggestions, and probably more questions. I had hoped this  would be simple. Maybe I'm making it too hard. Do ya think!
0
 

Author Comment

by:Qsorb
ID: 38777053
ScottPletcher

Looks promising but for the last part. I have no idea what to do with this:
...
) as derived
order by id desc

The ") as derived" confused me. Did you expect me to close the query after "order by id desc" or add something as what seems to be suggested in your answer? What do you mean by ") as derived"? Would you explain?

And catid is a number, if that's okay.
0
 

Author Comment

by:Qsorb
ID: 38777063
Dgrafx:


<cfquery name="front_page" datasource="ANEWS">
Declare @xamt int
Select @xamt = xamt from mainnews where online = 1
and catid = #CatID#
set rowcount '#@xamt#'
select *
    from mainnews
    where online = 1
    and catid = #CatID#
    order by id desc
set rowcount = 0 -- to reset
</cfquery>

set rowcount = 0 -- to reset

Hmm. Confusing. Not sure at all what you mean by  "-- to reset."
And did you mean verbatim to use #@xamt#, just like that in the query?
xamt is simply a tinyint number I have set in the database for each category, that is, how many stories to show for each category on the front page.
Look at your suggestion and let me know if this is correct and I'll try it.
0
 

Author Comment

by:Qsorb
ID: 38777094
Dgrafx:

I  believe your suggestion is hopelessly beyond me. I can't even begin to understand what set rowcount '#@xamt#' means. xamt is just the number of stories I want to show for each category and is hardcoded in the database. And I'm getting other errors.

I would have thought this would be a pretty straight forward question or perhaps I'm just not explaining what I want to do.

It's a news site's front page, main page, whatever you wish to call it. I will display a certain number of news stories saved in the database, obviously showing the latest news stories first. But I don't want to just show one story per news category. National News is more important than Apple News or Basketball. Each news category (catid) is represented by an small or tiny INT number. I'll show more of the same categories for more popular news categories but I still want to show only a certain amount of total stories on the front page whch will be determined, I guess, by my SELECT TOP 21, or I may decide on more stories, say 39.   And I may decide to display based on PUBLISHED date.

I get confused when I don't see the full cfquery written so that's why I included the following query, which of course, does not work for me.

<cfset xamt = 2> <!-- Just to get us going I hardcoded this --->

<cfset catid = 53> <!-- And hardcoded a category id --->

<cfquery name="front_page" datasource="ANEWS">
  declare @xamt int <!--- I have no idea what all this means so not sure how to use it --->
  select @xamt = xamt <!--- Confused on this too --->
  from mainnews where online = 1
  and catid = #CatID#
  set rowcount '#xamt#' <!---  Totally confuse on this --->
  select *
  from mainnews
  where online = 1
  and catid = '#CatID#'
  and online = 1
  and published = 1
  and dead = 0
  order by id desc
  set rowcount = 0 <!--- Confused about this too --->
</cfquery>

I'm hoping I can do something with  ScottPletcher's suggestion soon as he clarifies what he meant but the last line. Then I'll try all this again.
0
 

Author Comment

by:Qsorb
ID: 38777097
ScottPletche

Your suggestion looked promising but the problem is that I have at least 76 news categories and I'd be required to enter 76 different parts of the query WHERE CATID = 1, etc, etc.

Can you refine that suggestion so I may use it without having to enter all the category numers? Perhaps some kind of CFLOOP with the CATID?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 38777494
Qsorb, wich columns do you need your SELECT to return?
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 25

Expert Comment

by:dgrafx
ID: 38778463
qsorb
you changed what i posted !

just run what i posted - here it is again:

Declare @xamt int
Select @xamt = xamt from mainnews where online = 1 and catid = #CatID#
set rowcount @xamt
select *
    from mainnews
    where online = 1
    and catid = #CatID#
    order by id desc
set rowcount 0

don't change anything - just run this.
all you need do is supply your ColdFusion #CatID# variable.

post back as to if you were able to successfully run it and then we can discuss your questions.
I'm pressed for time right now but will be back later.

good luck
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 38778673
>> Can you refine that suggestion so I may use it without having to enter all the category numers? Perhaps some kind of CFLOOP with the CATID? <<

You could enter all the category numbers and row limits into a separate table.  It could be a temporary table if desired.  Then with one query we could join to that table and pull all the needed rows, sorted however you needed them sorted.
0
 

Author Comment

by:Qsorb
ID: 38780604
I see my intention of placing category numbers into the same table is not going to work. The MAINNEWS table is dynamic so I cannot hardcode the FEED, name, rsslink, catid numbers, etc into it.

So I created a separate table named FEEDS containing all news categories and each catid.

<cfquery name="FeedInfo" datasource="ANEWS">
select *
from feeds
where online = 1
</cfquery>

Dgrafx: So my problem is that I canot run your suggestion as is because I need the above FeedInfo query to populate the cfoutput cfloop.

This is the mess I have so far. Sorry for my confusion, but then, that's why I'm here.

<cfquery name="FeedInfo" datasource="ANEWS">
  select *
  from feeds
  where online = 1
</cfquery>


<!--- Should the following cfquery be in a cfloop to process all FeedInfo.catid's? --->
<!--- And remeber that the front page stands alone, not run by any other page so that's why I need call the catid's. --->

<cfquery name="front_page" datasource="ANEWS">
  Declare @xamt int
  Select @xamt = '#FeedInfo.xamt#'
  from mainnews
  where online = 1
  and catid = #FeedInfo.CatID#
  set rowcount @xamt
  select *
  from mainnews
  where online = 1
  and catid = #FeedInfo.CatID#
  order by id desc
  set rowcount 0
</cfquery>


<div class="container">
<cfoutput>
  <cfloop from="0" to="2" index="col">
    <div class="col#col#">
    <cfloop from="#col+1#" to="21" index="row" step="3">
       <div>
       #FeedInfo.name#
      </div>
    </cfloop>
  </cfloop>
</cfoutput>
</div>

Open in new window

0
 

Author Comment

by:Qsorb
ID: 38780605
ScottPletcher

Would you show me your last idea as code, after seeing my last code snippet?
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 38780770
ok - scratch what you are doing with setting the SQL variable to the value of the CF variable - again you modified what I posted!
I'm going to post something different that will probably get you where you are going - not the greatest of practices - but Sql Server 2000 is limited by today's standards ...

<cfquery name="feedinfo" datasource="ANEWS">
      select distinct xamt,catid
      from mainnews
      where online = 1
      order by id desc
</cfquery>
<cfloop query="feedinfo">
      <cfquery name="front_page" datasource="ANEWS">      
      select top (#feedinfo.xamt#) *
      from mainnews
      where online = 1
      and catid = #FeedInfo.CatID#
      order by id desc
      </cfquery>
      <cfloop query="front_page">
            put your front_page variables here
      </cfloop>
</cfloop>

post back as to how far along this came
you should also post some example data
0
 

Author Comment

by:Qsorb
ID: 38781394
Dgrafx:

xamt is a tinyint existing in the feeds table only.

I've attached a couple images of each table and hopefully that'll help.

I created the confusion with one of my first queries above, NewsInfo, then contradicted myself later by saying they were in the same table but different columns. I had begun that way but saw it was impossible. Sorry for that, not thinking straight. My dumb error.

So, to help clarify:

my FEED table contains: CATID, NAME, FEED_URL, ONLINE, XAMT

All values in the FEEDS table are static and unchanging until we update them manually. They simply define the values of each category (catid).

My MAINNEWS table contains: ID,NAME,STORY_BODY,ONLINE,TITLE, CATID,RSSLINK,POSTED,CONTENT as well as a couple other non-important columns.

But not to get you upset, I ran thFEEDS TableMainNews tablee code as given and of course got the error "Invalid column name 'xamt'.

XAMT is a hardcoded amount which determine how many news items of any particular category should be shown. I cannot put it into the mainnews table because those values are created dynamically from the actual rss content and from the settings in the FEED table.

So, obviously the FEED table contains xamt, not the MAINNEWS table.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 38782892
ok

<cfquery name="feedinfo" datasource="ANEWS">
      select CATID, NAME, FEED_URL, ONLINE, XAMT
      from FEED
      where online = 1
</cfquery>
<cfloop query="feedinfo">
      #CATID#, #NAME#, #FEED_URL#, #ONLINE#, #XAMT#<br>
      <cfquery name="front_page" datasource="ANEWS">      
      select top (#feedinfo.xamt#) ID,NAME,STORY_BODY,ONLINE,TITLE, CATID,RSSLINK,POSTED,CONTENT
      from MAINNEWS
      where online = 1
      and catid = #FeedInfo.CatID#
      order by id desc
      </cfquery>
      <cfloop query="front_page">
            #ID#,#NAME#,#STORY_BODY#,#ONLINE,TITLE#, #CATID#,#RSSLINK#,#POSTED#,#CONTENT#<br><br>
      </cfloop>
        <br>
</cfloop>
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 38782909
If I understood well, what you want can be accomplished by cursors. Solution should be something like:

DECLARE @SQLstatement NVARCHAR(4000)
DECLARE @TopCat INT
DECLARE @CatID VARCHAR(40)

DECLARE categories CURSOR FOR
SELECT catid, xamt FROM feeds

OPEN categories
FETCH NEXT FROM categories INTO @CatID, @TopCat

SET @SQLstatement = NULL

WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @SQLstatement IS NULL
			SET @SQLstatement = N'SELECT TOP ' + CAST(@TopCat AS VARCHAR) + ' * FROM MainNews WHERE online = 1 AND catid = '  + CAST(@CatID AS VARCHAR)
		ELSE
		                SET @SQLstatement = @SQLstatement + N' UNION ALL SELECT TOP ' + CAST(@TopCat AS VARCHAR) + ' * FROM MainNews WHERE online = 1 AND catid = '  + CAST(@CatID AS VARCHAR)
		FETCH NEXT FROM categories INTO @CatID, @TopCat

	END
		

CLOSE categories
DEALLOCATE categories

exec sp_executesql @SQLstatement

Open in new window

0
 
LVL 13

Expert Comment

by:PCIIain
ID: 38782962
I think part of the issue here is that there's then a Second top command over this result set.

There are many categories, each with a limit. When you return the top x in each of those categories (where x is different for each category) then you get Y results. You then want to resort these results by id desc and only get the top 21 of those correct?

The cursor stuff above (and most of the other code here) will get you the top 3 politics stories, even if they are four weeks old, then to top 5 sports stories, then the top 2 funny stories etc., etc.

I think you need to use this code to build a temporary table, and then run a
select top 21 * from temptable order by id desc
to get the actual list of info required.

I can write you this in sql TSQL code, but you need it in coldfusion code, which I've never used.

In fact, it can be done from the code above using the following change.
DECLARE @SQLstatement NVARCHAR(4000)
DECLARE @TopCat INT
DECLARE @CatID VARCHAR(40)

DECLARE categories CURSOR FOR
SELECT catid, xamt FROM feeds

OPEN categories
FETCH NEXT FROM categories INTO @CatID, @TopCat

SET @SQLstatement = NULL

WHILE @@FETCH_STATUS = 0
	BEGIN
		IF @SQLstatement IS NULL
			SET @SQLstatement = N'SELECT TOP ' + CAST(@TopCat AS VARCHAR) + ' * FROM MainNews WHERE online = 1 AND catid = '  + CAST(@CatID AS VARCHAR)
		ELSE
		                SET @SQLstatement = @SQLstatement + N' UNION ALL SELECT TOP ' + CAST(@TopCat AS VARCHAR) + ' * FROM MainNews WHERE online = 1 AND catid = '  + CAST(@CatID AS VARCHAR)
		FETCH NEXT FROM categories INTO @CatID, @TopCat

	END
		

CLOSE categories
DEALLOCATE categories

set @SQLstatement= N'select top 21 * from ('+@SQLstatement+') as alldata order by id desc'

exec sp_executesql @SQLstatement

Open in new window

0
 
LVL 13

Expert Comment

by:PCIIain
ID: 38782988
Alternatively, assuming you can affect the database using coldfusion markup, you need to do the following (and I can't code this for you as I don't know coldfusion).


1.

Create temptable/ empty temptable of contents. (temptable is a table with 1 column in it, id number)

2.

Using a loop on the quey "select catid,xamt from categories" insert into
temptable the top xamt id numbers from the records using your query above.

3.

Select from temptable joined to mainnews on id = id the top 21/39 records order by id

4.

drop temptable/ empty temptable of contents.
0
 

Author Comment

by:Qsorb
ID: 38784968
dgrafx:

Ran your suggestion as requested, got this error I can't seem to isolate and fix:


Error Executing Database Query.
 
 [Macromedia][SQLServer JDBC Driver][SQLServer]Line 1: Incorrect syntax near '('.  
 
 The error occurred in C:\SERVER\newsdomains\get\front-page.cfm: line 53
 
51 :       from MAINNEWS
52 :       where online = 1
53 :       and catid = #FeedInfo.CatID#
54 :       order by id desc


 
SQLSTATE   42000
SQL    select top (2) ID,NAME,STORY_BODY,ONLINE,TITLE,CATID,RSSLINK,POSTED,CONTENT from MAINNEWS where online = 1 and catid = 1 order by id desc  
VENDORERRORCODE   170
DATASOURCE   ANEWS
 

What went wrong and how should I fix?

I also wrapped the catid in quotes like this '#FeedInfo.CatID#' but same problem.

Thanks
0
 
LVL 25

Accepted Solution

by:
dgrafx earned 500 total points
ID: 38785139
i don't have a way to test sql 2000 so try removing the () from around the top xamt
so it would be :  select top #feedinfo.xamt#
0
 

Author Closing Comment

by:Qsorb
ID: 38785666
Okay, Dgrafx, that did the trick, removing the parentheses. At least the query seems to be outputting correctly, and the amount of xamt as defined in my table is correct, I think. I had no idea one could use a value from such a different source inside the cfquery.

Interesting. Will have to look and test a bit more. Gives me something to work with anyhow.

I'll use css to display three columns on the results.

If I need more help, that is, when I need more, I'll post another question. Thanks so much, again, as usual.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 38786955
glad it worked for you ...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now