LIMIT and ORDER BY

[jj20051]

I need mysql to select the highest id in a table, select the first 7 rows and flip the results so that the highest id is the last one displayed. (This is a while() function).

 

Example Table:

id, pageviews

1, 100

2, 200

3, 300

4, 400

5, 500

6, 600

7, 700

8, 800

9, 900

 

The query would pull out:

 

3, 300

4, 400

5, 500

6, 600

7, 700

8, 800

9, 900

 

Unfortunatly I don't know how to do this and some help would be appreciated. I hope I've explained it well enough for someone to understand what I'm trying to do. Its for a graph and I want the most recent data to be on the right side instead of the left which is what you'll get if you run a query like:

 

SELECT * FROM traffic ORDER BY id DESC LIMIT 7

[kickstart]

Hi

 

Afraid the only thing I can suggest is

 

SELECT *
FROM (SELECT * FROM traffic ORDER BY id DESC LIMIT 7 ) a
ORDER BY id

 

All the best

 

Keith

[Mchl]

Hi

 

Afraid the only thing I can suggest is

 

Why afraid? Perfectly valid solution.

[kickstart]

Why afraid? Perfectly valid solution.

 

Valid but I am not keen on using subselect except when necessary, and having over half the whole select being a small subselect just doesn't feel right to me.

 

All the best

 

Keith

[jj20051]

It works perfectly as far as I can tell... Is there something that is wrong with using a subselect or is it a design flaw on my part database wise?

[kickstart]

Hi

 

Subselects work fine, and performance wise are fine most of the time but they can struggle if the database cannot pass an index up to the outer select.

 

All the best

 

Keith

[Mchl]

You wouldn't need index here anyway. Just 7 rows to sort.

[kickstart]

You wouldn't need index here anyway. Just 7 rows to sort.

 

Mmmm, but never trust a system not to grow dramatically or people to change what they want (ie, how many rows required).

 

All the best

 

Keith

[jj20051]

This is a traffic log, while it may only need to pull 7 rows out at a time, imagine after a few weeks worth of traffic data how useful pulling only the latest data and putting it into a graph would be