MySQL - Limit

To limit the number of records returned from a table through a Node.js program, we need to execute the 'SELECT' statement with 'LIMIT' clause using the query() function of the mysql2 library as follows −

sql = "SELECT * FROM CUSTOMERS LIMIT 4";
con.query(sql);  

To limit the number of records returned from a table through a Java program, we need to execute the 'SELECT' statement with 'LIMIT' clause using the JDBC function executeUpdate() as follows −

String sql = "SELECT * FROM CUSTOMERS LIMIT 4";
statement.executeQuery(sql);

To limit the number of records returned from a table through a Python program, we need to execute the 'SELECT' statement with 'LIMIT' clause using the execute() function of the MySQL Connector/Python as follows −

limit_query = "SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_query)

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");

  //Selecting a Database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating a table
  sql = "CREATE TABLE CUSTOMERS (ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh',32, 'Ahmedabad', 2000.00 ), (2, 'Khilan',25, 'Delhi', 1500.00 ),(3, 'kaushik',23, 'Kota', 2000.00 ), (4,'Chaitali', 25, 'Mumbai', 6500.00 ), (5, 'Hardik',27, 'Bhopal', 8500.00 ), (6, 'Komal',22, 'MP', 4500.00 ), (7, 'Muffy',24, 'Indore', 10000.00 );"
  con.query(sql);

  sql = "SELECT * FROM CUSTOMERS LIMIT 4"
  con.query(sql, function (err, result) {
      if (err) throw err;
      console.log(result);
  });
});          

Output

The output produced is as follows −

Connected!
[
  {
    ID: 1,
    NAME: 'Ramesh',
    AGE: 32,
    ADDRESS: 'Ahmedabad',
    SALARY: '2000.00'
  },
  {
    ID: 2,
    NAME: 'Khilan',
    AGE: 25,
    ADDRESS: 'Delhi',
    SALARY: '1500.00'
  },
  {
    ID: 3,
    NAME: 'kaushik',
    AGE: 23,
    ADDRESS: 'Kota',
    SALARY: '2000.00'
  },
  {
    ID: 4,
    NAME: 'Chaitali',
    AGE: 25,
    ADDRESS: 'Mumbai',
    SALARY: '6500.00'
  }
]       

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class LimitQuery {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * FROM CUSTOMERS LIMIT 4";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String Id = rs.getString("Id");
              String Name = rs.getString("Name");
              String Age = rs.getString("Age");
              String Address = rs.getString("Address");
              String Salary = rs.getString("Salary");
              System.out.println("Id: " + Id + ", Name: " + Name + ", Age: " +  Age + ", Address: " + Address + ", Salary: " + Salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records: 
Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 25, Address: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Address: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 25, Address: Mumbai, Salary: 6500.00 

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
limit_value = 3
limit_query = f"SELECT * FROM tutorials_tbl LIMIT {limit_value}"
cursorObj.execute(limit_query)
result = cursorObj.fetchall()
print(f"First {limit_value} rows from Tutorial Table:")
for row in result:
    print(row)
cursorObj.close()
connection.close()           

Output

Following is the output of the above code −

First 3 rows from Tutorial Table:
(1, 'Learn PHP', 'John Paul', datetime.date(2023, 3, 28))
(2, 'Learn MySQL', 'Abdul S', datetime.date(2023, 3, 28))
(3, 'JAVA Tutorial', 'Sanjay', datetime.date(2007, 5, 6))