Any advice on using tickers?

[Anti-Moronic]

Hi, I'd love to hear your advice on this.

 

I have quite a few database tables which output data tables and use data from other tables. I'm currently stumped because I want to sort and order by certain columns which obtain their data via sql count queries. What is the desired approach?

 

Let's say I have a table full of hits, each with lots of details.

 

Then I have a table for categories.

 

I output my categories html table and in one of the columns is 'hits' which then counts the related hits in the hits table. But say I now want to order by hits?

 

Would it be ok for me to simply increment a column within the categories table called 'hits' thus making it simple to sort and order in the frontend.

[objnoob]

You can order by an aggregated function...

 

SELECT column1, count(column1)

FROM table1

GROUP BY column1

ORDER BY count(column1)

 

You can even give count(column1) an alias and reference it by that

[nogginj]

Ive done this using subqueries,

say

... SELECT u.user as user, (SELECT COUNT(hits) FROM hits) as hits FROM users ORDER BY hits

[objnoob]

nogginj: You can definitely take that route, but you'd probably need to group by at least column user in the nested/sub query. You'd also need to either specify a where (WHERE user.user = hits.user), or join the 2 tables (user and hits)  in the subquery. Your statement as shown would return the same count of hits for each user. This value happens to be the total of records in table hits with a hits column value specificed. Not the right idea.