domlao
domlao

Reputation: 16029

Make pagination in SQL with sorted records

I have 100 records, and this records contains, username(string) and groupname(string). And I want to 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.

Upvotes: 0

Views: 134

Answers (5)

gbn
gbn

Reputation: 432722

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

Upvotes: 0

royrui
royrui

Reputation: 1207

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

Upvotes: 0

Ben
Ben

Reputation: 1535

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.

Upvotes: 2

Sascha Galley
Sascha Galley

Reputation: 16101

ORDER BY groupname LIMIT 0, 10

where 0 is start and 10 is count of records

Upvotes: 1

Marco
Marco

Reputation: 57603

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

Upvotes: 1

Related Questions