CODEWITHSUNDEEP
mysqlsql-order-by
TPup
TPup

Reputation: 63

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

I have a database table that has two fields , date and name.

I want to have my query pull the first 20 by newest date first, then the rest of the query to pull the other elements by name alphabetically.

So that way the top 20 newest products would show first, then the rest would be ordered by name.

Upvotes: 1

Views: 1423

Answers (3)

Manjula
Manjula

Reputation: 5101

Use sql UNION operator to combine result of two SELECT queries.

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 ;

Upvotes: -1

pilcrow
pilcrow

Reputation: 58651

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 <= 20, rank, 21), name;

The innermost query, dummy, initializes our @rank variable. The next derived table, dateranked, ranks all rows by recency (breaking ties by name). The outermost query then simply re-orders the rows by our computed rank, treating ranks greater than 20 as rank #21, and then by name.

UPDATE: This query version is more compact, puts the conditional ranking logic in the outermost ORDER BY, uses IF() rather than CASE/END.

Upvotes: 4

Tom Haws
Tom Haws

Reputation: 1342

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`;

Upvotes: 0

Related Questions