Custom Paging by ORDER BY column and RAND() function using MySQL

Last Reply 5 months ago By dharmendr

Posted 5 months ago

Refering to the article custom paging in ASP.NET GridView using Mysql Stored Procedure (https://www.aspsnippets.com/Articles/Custom-Paging-in-ASPNet-GridView-using-MySql-Stored-Procedure.aspx)

Using northwind database, i am displaying from Customer table the following fields : CustomerID, ContactName, CompanyName, Country. I want to sort the Customers ordered by Country ASC and random, example : all customers in Argentina, then Brazil.And every time the customer name changed randomly but still the country is sorted in ASC I used this stored Procdure

 

CREATE DEFINER=`root`@`%` PROCEDURE `GetCustomers_Pager`(
	_PageIndex INT
   ,_PageSize INT
   ,OUT _RecordCount INT
)
BEGIN
	SET @RowNumber:=0;

	CREATE TEMPORARY TABLE Results
    SELECT  @RowNumber:=@RowNumber+1 RowNumber
		,CustomerID
		,ContactName
		,CompanyName
        ,Country
           FROM Customers WHERE Country != "" ORDER BY Country ASC, RAND();

	SET _RecordCount = (SELECT COUNT(*) FROM Results);

	SELECT * FROM Results
    WHERE RowNumber BETWEEN (_PageIndex -1) * _PageSize + 1 AND (((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1;

	DROP TEMPORARY TABLE Results;

END

But this sort the customers by Country in each page 

 

Posted 5 months ago

Then check with the below query. After geting random record then apply order by with country from results.

CREATE DEFINER=`root`@`%` PROCEDURE `GetCustomers_Pager`(
    _PageIndex INT
   ,_PageSize INT
   ,OUT _RecordCount INT
)
BEGIN
    SET @RowNumber:=0;
 
    CREATE TEMPORARY TABLE Results
    SELECT  @RowNumber:=@RowNumber+1 RowNumber
        ,CustomerID
        ,ContactName
        ,CompanyName
        ,Country
        FROM Customers WHERE Country != "" ORDER BY RAND();
 
    SET _RecordCount = (SELECT COUNT(*) FROM Results);
 
    SELECT * FROM Results
    WHERE RowNumber BETWEEN (_PageIndex -1) * _PageSize + 1 AND (((_PageIndex -1) * _PageSize + 1) + _PageSize) - 1
    ORDER BY Country ASC;
 
    DROP TEMPORARY TABLE Results;
 
END