Strange results from Limit x,y in query

[JamesButler]

I'm in Chapter 5 Introduction to SQL and working through some of the SQL queries to get a feel for doing this. I ran into some unexpected results while using the LIMIT x,y statement.

First I ran the query:

SELECT first_name, last_name
FROM users
ORDER BY registration_date ASC;

And I go back this result set:

MariaDB [sitename]> SELECT first_name, last_name
    -> FROM users
    -> ORDER BY registration_date ASC;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Larry      | Ullman    |
| Zoe        | Isabella  |
| Ringo      | Starr     |
| George     | Harrison  |
| Paul       | McCartney |
| John       | Lennon    |
| Michael    | Chabon    |
| Richard    | Brautigan |
| Russell    | Banks     |
| Homer      | Simpson   |
| Marge      | Simpson   |
| Bart       | Simpson   |
| Lisa       | Simpson   |
| Maggie     | Simpson   |
| Graham     | Greene    |
| Don        | DeLillo   |
| Jonathan   | Franzen   |
| David      | Jones     |
| Peter      | Tork      |
| Micky      | Dolenz    |
| Mike       | Nesmith   |
| David      | Sedaris   |
| Nick       | Hornby    |
| Melissa    | Bank      |
| Toni       | Morrison  |
| Abe        | Simpson   |
+------------+-----------+
26 rows in set (0.00 sec)

Then I ran the query:

SELECT first_name, last_name
FROM users
ORDER BY registration_date ASC
LIMIT 1,1;

And this is the result set:

MariaDB [sitename]> SELECT first_name, last_name
    -> FROM users
    -> ORDER BY registration_date ASC
    -> LIMIT 1,1;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Zoe        | Isabella  |
+------------+-----------+
1 row in set (0.00 sec)

As expected, I get back the second row and just return that one row since Zoe Isabella is the row at index 1.

So, I tried to return just Ringo Starr. He is at index 2, so, I need LIMIT 2,1. Ok

SELECT first_name, last_name
FROM users
ORDER BY registration_date ASC
LIMIT 2,1;

But I don't get Ringo, I get this:

MariaDB [sitename]> SELECT first_name, last_name
    -> FROM users
    -> ORDER BY registration_date ASC
    -> LIMIT 2,1;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Paul       | McCartney |
+------------+-----------+
1 row in set (0.00 sec)

What is going on? Paul is suppose to be at index 4!

Any Ideas?

 

[Larry]

I'd check the registration date. It's likely that multiple records have the same value there if they were created by a mass import.

[JamesButler]

Yes, I think you are correct. I was looking at that last night and noticed that a lot of those records had the same reg date because I pasted in your SQL in phpMyAdmin and ran it that way. 

I was monkeying around with XAMPP this morning and needed to add the Mercury mail server. In XAMPP, if you don't install everything the first time, you have to uninstall and reinstall choosing everything. The installation program will not let you choose an install directory that is not empty, so, I had to uninstall everything. In the process I forgot to backup the databases I created so far in the book! Yikes! So, I am starting over from scratch. I may just enter those user records in manually one-at-a-time so they have different reg dates.

Oh well, my mind is slipping. I'm getting old and should have know better, but hey, it's only development now.

 

[Larry]

Ah, cool! And thanks for sharing your learning experience. If it helps, I still make dumb mistakes, too.