Make pagination in SQL with sorted records

I have 100 records, and this records contains, username(string) and groupname(string). And I want to 开发者_Python百科sort the whole records by groupname or by username. Is there an SQL query string that I can sort first the whole records then limit the number of records? Or is there any way to do this kind of method?

Many thanks.

try this:

SELECT username, groupname FROM tablename ORDER BY groupname ASC LIMIT 0, 10

This selects both the username and groupname fields and orders them by groupname in Ascending order. Limit is taking two parameters, the starting offset and the number of records to return.

To sort you can try (MS-SQL):

SELECT TOP 10 
username, groupname
FROM table
ORDER BY username,groupname

or for MySQL:

SELECT username, groupname
FROM table
ORDER BY username,groupname
LIMIT 0,10

ORDER BY groupname LIMIT 0, 10

where 0 is start and 10 is count of records

SELECT username, groupname FROM table ORDER BY username LIMIT 0, 10

You need some way to pass in the desired sorting preference, such as a variable

DECLARE sort VARCHAR(10);
SET sort = 'group'

SELECT
   username, groupname
FROM
   tablename
ORDER BY
   CASE
      WHEN sort = 'group' THEN groupname
      WHEN sort = 'user' THEN user
   END
LIMIT 0, 10