JamesButler Posted November 29, 2019 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? Link to comment Share on other sites More sharing options...
Larry Posted December 2, 2019 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. Link to comment Share on other sites More sharing options...
JamesButler Posted December 2, 2019 Author 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. Link to comment Share on other sites More sharing options...
Larry Posted December 4, 2019 Share Posted December 4, 2019 Ah, cool! And thanks for sharing your learning experience. If it helps, I still make dumb mistakes, too. Link to comment Share on other sites More sharing options...
Recommended Posts