0

I'd like to make an query such as: select * from somefield order by name

The order will ofcourse be alphabetical and i'd like for each letter of the alphabet to be showed only 5 hits. Is this possible with the query itself or should i be using php to make queries for each letter of the alphabet?

The number of rows returned this way can only be 5 x 26 rows long max.

Mart
  • 475
  • 4
  • 21

1 Answers1

1

Most databases support the ANSI standard windows functions. For this purpose, you can use row_number():

select t
from (select t.*,
             row_number() over (partition by left(name, 1) order by name) as seqnum
      from t
     ) t
where seqnum <= 5;

Note: Not all databases support left(), to you might need substr(name, 1, 1) or substring(name, 1, 1).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm doing this in my phpmyadmin but this ain't working. Nor the left() or the substr() is working there. – Mart May 24 '16 at 10:05