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

Last Reply one year ago By dharmendr

Posted one year 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 one year 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