Gossamer Forum
Home : Products : Gossamer Links : Discussions :

group by with limit clause

Quote Reply
group by with limit clause
Hi there,

Is there anyway to do a GROUP BY statement with LIMIT in such a way that LIMIT acts on the Groups found as opposed to the hits found?

In other words, here's my select option statement:

$db->select_options("Group by Add_Date Order by Add_Date asc Limit 5,3");
$sth = $db->select ({Username =>$user}

Now the problem is that the results from such a query are based on the first 5 rows of data that match the query, and what I need is to be able to bring up all the results for the first 5 groups found.

i.e..

group 2003-11-01 might have 5 hits and
group 2003-11-02 might have 15 hits.
etc... thru group5

I have already tried manually limiting the number of groups that I want by excluding the Limit cause, but as that returns all the records (several thousand), it takes longer each time (with new data constantly being added) to scroll through each row and either dump it or keep it depending on whether i'm looking for the equivalent of nh=1, 2, 3, 4, 5 etc....

How else could a query be constructed to do this?

thanks for you help.

peace.

Kyle
Quote Reply
Re: [klangan] group by with limit clause In reply to
Which version of mysql are you using?

Last edited by:

afinlr: Nov 19, 2003, 2:14 AM
Quote Reply
Re: [afinlr] group by with limit clause In reply to
Hi, thanks for responding.

I'm using mysql version: 3.23.41

peace.
Quote Reply
Re: [klangan] group by with limit clause In reply to
I think it should work. I would try typing the command you are trying to execute into mysql monitor (by the way, I don't think you need the order by). Something like this?

SELECT Add_Date FROM Links WHERE LinkOwner = 'admin' Group by Add_Date ASC Limit 5,3
Quote Reply
Re: [afinlr] group by with limit clause In reply to
Hi thanks for the reply, however, that doesn't solve the problem.

If you were to run that query, you would only get five links starting at page 3. What i'm looking for is to get 5 groups starting from page 3 which could easily mean that group 1 has 10 links, group2 has 3 links, group3 has 9 links. (as you can see that's actually 22 links instead of 5. So I need 5 groups, not 5 links.) The limit and offset only work on hits.

There is a similar situation with the GT's search script when the value of:
Setup->Build->build_search_gb is set to 1.

In that script when that value is checked and you do a search you will find a category on page 1 and then you can often find that category appear again on another page. The build_search_gb, does not necessarily provide you *all* the found hits for a particular category on the same page (they're spread out over all the search pages because of the limit and offset clauses.) One way way to get *all* the hits grouped under the respective category is to search the entire database, then group the data and then manually figure out the nh and offset values during the print process. That's what I'm doing now, but as the database grows, it's beginning to take way to long to processs all the data and output the page (7-9 seconds currently). There has got to be a better way.

I'm currently thinking that I need to first do a DISTINCT query to get the first x gb values. Then put those results into a condition, then do my real search with a condition of AND ("$gb", "in", "\@gb_values") - without a Limit clause.

Is there a better way?

thanks again for your reply.

peace.
Quote Reply
Re: [klangan] group by with limit clause In reply to
I think that this is a misunderstanding of what group by does. To do what you want (as far as I understand it) I think you would need to run multiple queries.

I think you would need to do one query to get the group and then within that you would need to pull out the individual links for that group - using a while statement. So you could use the limit on the main query which I think would acheive what you want? If I've misunderstood, let me know.
Quote Reply
Re: [afinlr] group by with limit clause In reply to
Thanks for the reply. Your suggestion of multiple queries is exactly where I was headed next.
I was just hoping MySQL had solution for doing it in one query.

Anyway, thanks for the conversation, it really helped me talk out the problem and figure out a solution.

peace.