Jump to content
Larry Ullman's Book Forums

Recommended Posts

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?

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...