JamesButler 0 Posted November 29, 2019 Report Share Posted November 29, 2019 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? Quote Link to post Share on other sites
Larry 429 Posted December 2, 2019 Report Share Posted December 2, 2019 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. Quote Link to post Share on other sites
JamesButler 0 Posted December 2, 2019 Author Report Share Posted December 2, 2019 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. Quote Link to post Share on other sites
Larry 429 Posted December 4, 2019 Report Share Posted December 4, 2019 Ah, cool! And thanks for sharing your learning experience. If it helps, I still make dumb mistakes, too. Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.