CODEWITHSUNDEEP
mysqlsql
EdanB
EdanB

Reputation: 1486

How do I retrieve results from Mysql in this order?

I have a table with a column, lets call it "query", which is a varchar.

I would like to retrieve the values into a paginated list in such a way that each page will contain 208 results, 8 from each letter in the alphabet.

So on page 1 the first 8 results will begin with "a", the next 8 will begin with "b", and so on until "z" (if there aren't any results for that letter then it just continues to the next letter.

On page 2 of the results it would show the next 8 results beginning with "a", the next with "b", and so on.

Basically instead of sorting by query ASC, which will result in the first page having all words beginning with "a", I would like each page to contain words beginning with each letter of the alphabet.

If you feel I did not explain myself properly (I do!), then please feel free to ask. I have the idea in my head but it's not easy translating it into words!

Upvotes: 0

Views: 79

Answers (4)

Nicola Cossu
Nicola Cossu

Reputation: 56357

I would never use this query on large dataset but just for personal fun I found this solution:

select *,
ceil(row_num/8) as gr 
from (
select
*,
@num := if(@word = substring(word,1,1), @num + 1, 1) as row_num,
@word := substring(word,1,1) as w
from words,(select @num:=0,@word:='') as r order by word ) as t
order by gr,word

Upvotes: 0

Darth Plagueis
Darth Plagueis

Reputation: 930

you can use group by the first letter using 

group by left(query, 1)

And from the server side script, show 8 results from the query result (perhaps by using the page page number * 8 and starting from that index for each letter, starting from page 0, that is)

Upvotes: 0

Timur
Timur

Reputation: 6718

I think this is not possible to do in one statement (or, may be possible, but too slow in execution). May be you must take a look on MySQL prepared statements or change behavior of the page.

Upvotes: 0

Johan
Johan

Reputation: 76537

a naive start approach could be:

SELECT * FROM table1 WHERE `query` LIKE 'a%' ORDER BY `query` LIMIT 8
UNION
SELECT * FROM table1 WHERE `query` LIKE 'b%' ORDER BY `query` LIMIT 8
UNION
SELECT * FROM table1 WHERE `query` LIKE 'c%' ORDER BY `query` LIMIT 8
....

The second page would need to be done using

SELECT * FROM table1 WHERE `query` LIKE 'a%' ORDER BY `query` LIMIT 8,8
UNION
....

Third page:

SELECT * FROM table1 WHERE `query` LIKE 'a%' ORDER BY `query` LIMIT 16,8
UNION
....

etc.

Upvotes: 1

Related Questions