How to get the data and the amount of data simultaneously

Question

This query would get one row with the id and the number of ID in the Messages.

SELECT id, count (id) FROM Messages

and I need that could get a list of id in the second field was the number

EDIT:

I do paged media. To do this I need to know how many of these lines that would make the button "next" active or not active

I used C#

Answer 1

Are you saying you want the total number of columns? If that's the case and you're using php, you can call mysql_num_rows on the query result; there's probably something similar for other platforms.

Answer 2

It sounds like you're trying to get both the id of the first row number and the overall count of rows in a single row of data, so you don't have to either do two queries or run a query returning a lot of rows just for the purpose of finding out how many rows it returns.

Try something like this:

SELECT MIN(id), COUNT(id) AS count
  FROM Messages

or, if you want the first batch of data rows matching some criterio along with the count you could try something like this:

SELECT detail.id, detail.message_text, detail.user, summary.count
  FROM (SELECT id, message_text, user
          FROM Messages
         WHERE user = 'user'
         ORDER BY id ASC) detail
  JOIN (SELECT COUNT(*) count
          FROM Messages
         WHERE user = 'user') summary
  LIMIT by 0,10

If you put indexes on your user and id columns this query will be decently efficient. All the rows will have the same value for summary.count, which is the number you want to control your next-page button's visibility.