Sorting by a to z with Limit

Question

I have one table customers which has one field 'name' with a to z names records.

I get records from a to z with asc query

SELECT * FROM `customers` ORDER BY name ASC

But how can i get 5 records which starts with all a to z alphabets with only one query?

Output:

a
a
a
a
a
b
b
b
b
b and so on to z. Thanks in advance.

Does this need to be done as a single sql statement/query, or can a stored procedure/function be called to provide the results? — Raad, Jan 20 '14 at 12:38
Related: http://stackoverflow.com/questions/6656316/mysql-limit-occurences-of-a-particular-column-value — thicolares, Jan 20 '14 at 12:39

score 1 · Accepted Answer · answered Jan 20 '14 at 12:37

1

Try this:

SELECT c.name
FROM (SELECT c.name, IF(@lastLetter=@lastLetter:=LEFT(c.name, 1), @cnt:=@cnt+1, @cnt:=0) letterCnt
      FROM customers c, (SELECT @lastLetter:='', @cnt:=0) A 
      ORDER BY c.name ASC
    ) AS c
WHERE c.letterCnt < 5

answered Jan 20 '14 at 12:37

Saharsh Shah

28,687
8
48
83

Saharsh Shah i am getting result in mysql but not in sqlite. Can u help me for sqlite? – Tony Stark Jan 20 '14 at 12:46
@TonyStark Sorry, I'm newbie to sqllite. – Saharsh Shah Jan 20 '14 at 13:02
I am getting this error [unrecognized token: ":"], if u can help me. – Tony Stark Jan 20 '14 at 13:04
@TonyStark Sorry but I can't help you for SQLLite – Saharsh Shah Jan 20 '14 at 13:06
Ok np. Thanks for the answer but i will accept it later. i need it in sqlite also(but i can give u upvote). can u little bit explain about ur query? its amazing. – Tony Stark Jan 20 '14 at 13:09

GercoOnline · Answer 2 · 2014-01-20T13:09:31.753

When you use SQL you can use functions like Rank, DENSE_RANK and ROW_NUMBER

DECLARE @Customer AS TABLE
(
    Id int,
    Name varchar(50)
)

INSERT INTO @Customer VALUES
(1, 'aa'),
(2, 'ab'),
(3, 'ac'),
(4, 'ba'),
(5, 'bb'),
(6, 'bc'),
(7, 'ca'),
(8, 'cb'),
(9, 'cc')

select *
from (
    select *, 
        RANK() OVER(PARTITION BY SUBSTRING(Name, 1, 1) ORDER BY Name ASC) AS [Rank] 
    from @Customer
) tableWithRank
WHERE tableWithRank.Rank <= 2

Output:

1|  aa|1

2|  ab|2

4|  ba|1

5|  bb|2

7|  ca|1

8|  cb|2

Sorting by a to z with Limit

2 Answers2