MySQL Query with first 20 items ordered by one field and rest ordered by name ASC?

It's a bit ugly, but you can do it in one query.

It's a bit ugly, but you can do it in one query: SELECT name, `date` FROM ( SELECT @rank := @rank + 1 AS rank, name, `date` FROM (SELECT @rank := 0) dummy JOIN products ORDER BY `date` DESC, name) dateranked ORDER BY IF(rank UPDATE: This query version is more compact, puts the conditional ranking logic in the outermost ORDER BY, uses IF() rather than CASE/END.

This is a very nice answer. If it actually works, I hope the asker marks it as the accepted answer. – Tom Haws Jan 6 at 5:57 Thanks, @TomHaws.

I updated with a clearer and equivalent version of the query. – pilcrow Jan 6 at 15:47 I really like that you implemented IF instead of CASE. Now I need to study your query, as it uses some stuff I don't know!

– Tom Haws Jan 6 at 15:55 p.s. Your solution is not ugly at all. In fact, I think it's a thing of beauty, especially since you changed the CASE to an IF.

Very elegant and instructive sample query. – Tom Haws Jan 6 at 16:05.

I'm afraid this has to be done by adding a special column to your table or creating a temporary table, TPup. If you let me know whether you are interested in those options, I'll tell you more. The two queries option like the following might be a possibility, but my version of MySQL tells me LIMIT isn't available in sub-queries.

SELECT `date`, `name` from `table` ORDER BY `date` LIMIT 0, 20; SELECT `date`, `name` from `table` WHERE `id` NOT IN (SELECT `id` from `table` ORDER BY `date` LIMIT 0, 20) ORDER BY `name`.

Select the top 20 order by the date then select all order by name where id not in (select id's for the top 20 order by the date) Of course you'll have to write the actual SQL.

You can even omit where record id not in (select id's for the top 20 order by date). As long as UNION by default has DISTINCT behaviour thus will cut all duplicates – zerkms Jan 6 at 1:26 1 good point zerkms – Michael Durrant Jan 6 at 1:27 +1 for typoed food point ))) – zerkms Jan 6 at 1:28 actually I'm not sure if this will list them first or just mix them in? – Michael Durrant Jan 6 at 1:28 it won't.At least I've never experienced such issues – zerkms Jan 6 at 1:29.

Use sql UNION operator to combine result of two SELECT quries. According to MySQL docs: use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. ... To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one.

The following example uses both clauses: (SELECT a FROM t1 WHERE a=10 AND B=1) UNION (SELECT a FROM t2 WHERE a=11 AND B=2) ORDER BY a LIMIT 10; Edit: I missed the part that explain OP needs to sort one set of the result on the date and the other set of the result alphabetically. I think you need to create a temporary field for the sorting purpose. And SQL query would be something similar to this.(SELECT *, 'firstset' as set_id FROM t1 ORDER BY date LIMIT 0, 20) UNION (SELECT *, 'secondset' as set_id FROM t1 ORDER BY date LIMIT 20, 18446744073709551615) ORDER BY CASE WHEN set_id='firstset' THEN date WHEN set_id='secondset' THEN name END DESC.

When you sort each subset separately - there is a chance to get the performance improved by indexes. When you sort the whole query - there is no such chance and all the data will be sorted in memory (not good) – zerkms Jan 6 at 3:42.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.