Menu
Community /Pin to ProfileBookmark
@NogDogMay 16.2006 — #is this a fast query? My main concern is then this get up in to the 100,000 records.
I am considering running a query every 1 hour and store the results in a seperate table holding regularly updated stats for the site.
what kind of index's should I set up to optimize querys
It should be a fast query, I don't see anything algorithmically wrong with it at all. It's a great solution to what you're wanting to do.I think there may have been a misunderstanding here - from the OP it appears that the data in question may not have one row per id, each with a "views" column? Otherwise why is he talking about grouping it by id and counting the views?
Markbad311, can you confiirm the tables and structure you are using, otherwise I think you may be getting a duff steer...?
You might want something more like
SELECT id, Name, count(*) AS Views, pic, url FROM table_name GROUP BY id ORDER BY Views DESC LIMIT 2
but I can't be sure until I see tables.
HTH,
Dai yea I do have a problem. I forgot their is no views field. Everytime the page is viewed a row is created. The row is the actual "View" each row indicates one view.
Now I need to count how many rows match each ID in the table, them only display the top TWO.
Sorry for any confusion I said it worng and I apologize.
Did you try this - it groups the results by id and then uses count to get an alias called views that will behave the same as an actual column called views for your purposes...I seen this and I actually have a query like it and I was messing with that one but again forgot about the LIMIT parameter so I kept getting lost.
I will implement it later today and report back!! Thanks a lot people of web developer!
Query
oops dumb mistake all fixed it works like it is supposed to. just how can I make it faster, and all in sql formatting. I know CASE but how to use CASE in a complicated query such as these two combined and grouped by id orderded by views, from most to least.
thanks chazz... Yea I forgot that it works as expected. Chazz your a MySql Guru, what do you think about combining these query's?
[RESOLVED] read a DB select two records with highest occurrences
I am preparing to implement profiles for users. So here is the scoop.
Four things have to happen here:
Select all the fields in a record (for later display)
group all the results by an ID number (to ensure no repeats)
count how many views for each ID Number
only return two top ID’s with most views
the fields in p_page there are more fields in the DB but these I need for display. (mock up).
id(AutoInc), Name, Views, pic, url
so basically whichever 2 “id” have the most “Views” will return as the result. Anyone have any ideas?
thanks in advanced.
Sign in
to post a comment14 Comments(s) ↴
0
@NogDogMay 16.2006 — #<i>
</i>SELECT id, Name, Views, pic, url FROM table_name ORDER BY Views DESC LIMIT 2
reply?
0
@Markbad311authorMay 16.2006 — #I am considering running a query every 1 hour and store the results in a seperate table holding regularly updated stats for the site.
what kind of index's should I set up to optimize querys
reply?
0
@jonraMay 16.2006 — #is this a fast query? My main concern is then this get up in to the 100,000 records.
I am considering running a query every 1 hour and store the results in a seperate table holding regularly updated stats for the site.
what kind of index's should I set up to optimize querys[/QUOTE]
It should be a fast query, I don't see anything algorithmically wrong with it at all. It's a great solution to what you're wanting to do.
reply?
0
@DaiWelshMay 16.2006 — #Markbad311, can you confiirm the tables and structure you are using, otherwise I think you may be getting a duff steer...?
You might want something more like
SELECT id, Name, count(*) AS Views, pic, url FROM table_name GROUP BY id ORDER BY Views DESC LIMIT 2
but I can't be sure until I see tables.
HTH,
Dai
reply?
0
@Markbad311authorMay 17.2006 — #Now I need to count how many rows match each ID in the table, them only display the top TWO.
Sorry for any confusion I said it worng and I apologize.
reply?
0
@DaiWelshMay 17.2006 — #
SELECT id, Name, count(*) AS Views, pic, url FROM table_name GROUP BY id ORDER BY Views DESC LIMIT 2
[/QUOTE]
Did you try this - it groups the results by id and then uses count to get an alias called views that will behave the same as an actual column called views for your purposes...
reply?
0
@Markbad311authorMay 17.2006 — #I will implement it later today and report back!! Thanks a lot people of web developer!
reply?
0
@bokehMay 17.2006 — #is this a fast query? My main concern is then this get up in to the 100,000 records.No need to do that. MySQL does it's own caching. The cached data is stored until the table is modified in some way (i.e. an INSERT or UPDATE query is run on the table).
I am considering running a query every 1 hour and store the results in a seperate table holding regularly updated stats for the site. [/QUOTE]
reply?
0
@Markbad311authorMay 18.2006 — #This is what I got so far.Query
[code=php]
function HotBands($connection)
{
$lnkHotBands = "<a href="http://www.eriescene.com/erie_local_bands/" title="Erie Bands">New Bands</a>";
$hot_bands = "<div class="section250 left ">
<h5 class="heading picBlue250 txtWhite">$lnkHotBands</h5>";
$sqlCountBand = "
SELECT b_id, count(b_id) AS views
FROM band_track
GROUP BY b_id
ORDER BY views
LIMIT 3";
$resCountBand = mysql_query($sqlCountBand) or die(mysql_error());
while($rowCountBand = mysql_fetch_array($resCountBand))
{
$views = ($rowCountBand['views']);
$b_id = ($rowCountBand['b_id']);
$sqlHotBand = "SELECT b_id, band, genre, web_site
FROM band_list
WHERE b_id = '$b_id'";
<i> </i> $resHotBand = mysql_query($sqlHotBand) or die(mysql_error());
<i> </i> while($rowHotBand = mysql_fetch_array($resHotBand))
<i> </i> {
<i> </i> $lnkHotBand = ($rowHotBand['band']);
<i> </i> $strHotGenre = ($rowHotBand['genre']);
<i> </i> $lnkHotSite = ($rowHotBand['web_site']);
<i> </i> $hot_bands .="
<i> </i> <div class="advert h15 fsection ">
<i> </i> <h5 class="txtBlack">$lnkHotBand</h5>
<i> </i> Genre: $strHotGenre<br />
<i> </i> $views $lnkHotSite
<i> </i> </div>";
<i> </i> }
<i> </i> }
<i> </i> $hot_bands .= "</div>";
<i> </i> print $hot_bands;
<i> </i>}
HotBands($connection);
[/code]</CODE>
band_track tracks all page displays regarading bands.<br/>
fields in band_track:<C>id`b_idb_dateb_timeb_ipb_agentb_referrer
band_list contains all information about each band
fields in band_list:b_idbandcitystategenreweb_sitebanner
the relation between each of the tables is b_id, when the page is displayed a row is inserted in to band_track using the band_list id number as the relation between the two table records.
What I need to do is calculate how many views each band recieved from band_track and then select data from the matching b_idreply?
0
@Markbad311authorMay 18.2006 — #by the way the above function gives me an errorTable 'markbad_markbadsql.band_list WHERE b_id = '0'' doesn't existoops dumb mistake all fixed it works like it is supposed to. just how can I make it faster, and all in sql formatting. I know CASE but how to use CASE in a complicated query such as these two combined and grouped by id orderded by views, from most to least.
reply?
0
@chazzyMay 18.2006 — #because you open your backtick but never close it<i>
</i>FROM <span><code>band_list
</CODE>
is missing the reply?
0
@Markbad311authorMay 18.2006 — #reply?
0
@chazzyMay 18.2006 — #what, like this:SELECT b_id, count(b_id) AS views , l.band, l.genre, l.website FROM band_track, band_list l WHERE b_id = l.b_id GROUP BY b_id ORDER BY <span><code>views</code></span> LIMIT 3;reply?
0
@Markbad311authorMay 20.2006 — #see thats the ^&%* I am talking about. Chazz your the man! I don't know how you know but you know everytime. good lookin bro. This topic is resolved.reply?