Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ALi.

Asked: March 04, 2021 - 7:29 am UTC

Last updated: March 10, 2021 - 1:36 am UTC

Version: Pycharm sql

Viewed 100+ times

You Asked

I have a database and I want to print the following,
The last 10 rows
The first 10 rows
The largest zipcodes number
The smallest zipcodes number

This is the code I used.

import pymysql

db = pymysql.connect(host="192.168.0.180",port=4000,user="maxuser",passwd="maxpwd")
cursor=db.cursor()
cursor.execute("SELECT * FROM zipcodes_one.zipcodes_one where <= MAX_ROWS")
results=cursor.fetchall()
for result in results:
print (result)

Please help
Thank you


and we said...

"The last 10 rows" based on what ? We need a definition of "last". I'll assume you have column on this table called DATE_CREATED

- The last 10 rows
- The first 10 rows
- The largest zipcodes number
- The smallest zipcodes number

would have queries as:

select * from zipcodes_one.zipcodes_one order by date_created desc fetch first 10 rows only
select * from zipcodes_one.zipcodes_one order by date_created asc fetch first 10 rows only
select * from zipcodes_one.zipcodes_one order by zipcode_number desc fetch first 1 row only
select * from zipcodes_one.zipcodes_one order by zipcode_number asc fetch first 1 row only




Rating

  (3 ratings)

Comments

error

Ali, March 05, 2021 - 9:49 am UTC

Hello thank you so much for your response,
I'm getting an error after adding any of the queries you mentioned and I don't know how to solve it
I am still learning python

This is the error

pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'fetch first 10 rows only' at line 1")

Another approach, and using MySQL syntax

Mark Homoky, March 06, 2021 - 3:10 pm UTC

Your error isn't a Python one, it's a question of SQL syntax which differs between Oracle Database (which Connor was referring to), and MySQL / MariaDB.

As you can see from the documentation [1] MySQL does not support the fetch first 10 rows clause, but instead has the limit 10 rows clause instead. You should be able to adjust Connor's query by substituting as above to get your desired result.

On a broader level, there's another thread on AskTom discussing the topic of how to determine the meaning of first and last more extensively for Oracle Database [2] which uses a SQL subseslect to get the correct result, but the conceptual reasoning applies to SQL in general so it's a worthwhile read if you're interested.

It's critical to understand the measure against which you're creating the query, and the supposed reason behind the need for it, in real-world scenarios. See the later comments at [2] for March 5th 2021 in which a reader explains their reasoning as needing to populate an additional table at insert time, with the child record in the details table linked back to a parent table. The solution in this case was insert into... returning... and is a great example of proper problem analysis.

Using the same assumptions as Connor, having a column called DATE_CREATED, here's another approach you might try which may be easier to understand; this uses Common Table Expressions and is probably overkill in this situation:

WITH FirstTen as ( SELECT * FROM zipodes_one.zipcodes_one ORDER BY DATE_CREATED ASC )
 SELECT * FROM FirstTen WHERE ROWNUM <=10;

WITH LastTen as ( SELECT * FROM zipodes_one.zipcodes_one ORDER BY DATE_CREATED DESC )
 SELECT * FROM LastTen WHERE ROWNUM <=10;

WITH FirstZip as ( SELECT * FROM zipodes_one.zipcodes_one ORDER BY DATE_CREATED ASC )
 SELECT * FROM FirstZip WHERE ROWNUM =1;

WITH LastZip as ( SELECT * FROM zipodes_one.zipcodes_one ORDER BY DATE_CREATED ASC )
 SELECT * FROM LastZip WHERE ROWNUM =1;


The Common Table Expression or CTE is documented for MySQL at [3], linked below.

Small tip: when asking a question and it asks for version, it's referring to the database verison you're working with.

Hope this helps - good luck :-)

[1] https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/select.html
[2] https://asktom.oracle.com/pls/apex/asktom.search?tag=fetching-last-record-from-a-table
[3] https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html
Connor McDonald
March 10, 2021 - 1:36 am UTC

Great input - thanks Mark.

Correcting my error

Mark Homoky, March 06, 2021 - 3:25 pm UTC

Oops :-/

Spot the deliberate error in my reply... correct code for the second two statements is below:

WITH FirstZip as ( SELECT * FROM zipodes_one.zipcodes_one ORDER BY zipcode_number ASC )
 SELECT zipcode_number FROM FirstZip WHERE ROWNUM =1;

WITH LastZip as ( SELECT * FROM zipodes_one.zipcodes_one ORDER BY zipcode_number DESC )
 SELECT zipcode_number FROM LastZip WHERE ROWNUM =1;


but for simplicity in this instance should really be written as:

SELECT zipcode_number FROM zipodes_one.zipcodes_one ORDER BY zipcode_number ASC LIMIT 1

SELECT zipcode_number FROM zipodes_one.zipcodes_one ORDER BY zipcode_number DESC LIMIT 1


(I really wish the window to enter replies was a little larger on the form... Connor?)

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.