CODEWITHSUNDEEP
phpmysqlphpmyadmin
Jeniffer
Jeniffer

Reputation: 97

How to count rows in mysql query?

I want to query mysql according to row number ? suppose I want to query last 10 rows , how to do this in mysql query?

Here is my mysql query, but it queries according to 6 days interval, But I need to query it according to last 10 rows instead of 6 days interval,

SELECT people_id, people_number 'people number', count(people_number) 'Occurs',
  (
   Select count(people_number) from people_history
   where people_id =  5486 and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
  ) 'Total',
  count(people_number)/(
      Select count(people_number) from people_history
      where people_id = 5486 and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
  )*100 'Percent'
FROM people_history
where people_id = 5486 and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
group by people_id, people_number
order by Percent desc`

Upvotes: 0

Views: 955

Answers (5)

AndrewR
AndrewR

Reputation: 6758

LIMIT will restrict the number of results returned. It goes at the end of the query. Take a look at the documentation for SELECT, and go down to the LIMIT section.

For your query, if you add LIMIT 10 at the end, you will get only the 10 results you are looking for. Combine LIMIT with ORDER BY (ASC or DESC) to get the first or last results.

Upvotes: 1

Dave
Dave

Reputation: 29141

Reverse your ORDER, so the last 10 are the first 10, the LIMIT by 10.

Upvotes: 1

tipanverella
tipanverella

Reputation: 3677

well "last" has to refer to some sort of ordering (order by .... ) statement. you always add limit N (N=10 in your case)

Upvotes: 0

John Conde
John Conde

Reputation: 219874

Make your query this:

SELECT * FROM people_history ORDER BY id DESC LIMIT 10

Limit allows you to limit the amount of results you get from a query. By sorting by ID you can get the last 20 results in your table.

Upvotes: 4

CodeCaster
CodeCaster

Reputation: 151674

LIMIT:

SELECT 
    people_id,
    people_number 'people number',
    count(people_number) 'Occurs',
    (Select 
        count(people_number) 
    from 
        people_history 
    where 
        people_id = 5486 
        and date > DATE_SUB(NOW(), INTERVAL 6 DAY)) 'Total',            
    count(people_number) / 
        (Select 
            count(people_number) 
        from 
            people_history 
        where 
            people_id = 5486 
            and date > DATE_SUB(NOW(), INTERVAL 6 DAY)) *100 'Percent' 
FROM 
    people_history 
WHERE
    people_id = 5486 
    and date > DATE_SUB(NOW(), INTERVAL 6 DAY)
GROUP BY
    people_id, people_number 
ORDER BY 
    Percent desc
LIMIT 0, 10

And you might want to rewrite that query a little.

Upvotes: 1

Related Questions