Is There an Alternative to Top in MySQL

Is there an alternative to TOP in MySQL?

Ordering and limiting the results:

SELECT field1, field2
FROM myTable
ORDER BY field1 ASC
LIMIT 10

What is the right syntax for MySQL TOP?

The equivalent "TOP" syntax in MySQL is "LIMIT":

So:

SELECT TOP 2 * FROM `sql_tbl`

becomes:

SELECT * FROM `sql_tbl` LIMIT 2

PHP SQL Server Alternative for TOP and LIMIT of MYSQL

Look at these posts. Simulating LIMIT from MySQL is little bit tricky.

LIMIT 10..20 in SQL Server

How to implement LIMIT with Microsoft SQL Server?

From SQL server 2012 there is OFFSET and FETCH.

SELECT * FROM Table ORDER BY FirstName OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

It should be equivalent to LIMIT 10, 5.

Getting the Top N Results Including Ties

I don't know your table structure so I'm going to make some assumptions here, so adjust the table and column names as needed.

The first step would be get the top 3 scores:

SELECT DISTINCT
    `score`
FROM 
    `points` 
LIMIT 3

Then get all people with those scores, so putting it together gets:

SELECT
    `name`, -- Whichever fields you have
    `score`
FROM
   `points`
WHERE
    `score` IN (
         SELECT DISTINCT
             `score`
         FROM 
             `points` 
         LIMIT 3
    )
ORDER BY
    `points` DESC, -- Sort highest score first
    `name` ASC; -- When tied, sort alphabetically

Alternative SQL ANSI for TOP WITH TIES

Here is a third option for SQL Server:

WITH cte AS (
    SELECT p.id_category, COUNT(*) AS cnt
    FROM product p 
    INNER JOIN category c
        ON p.id_category = c.id_category
    GROUP BY p.id_category
)

SELECT *
FROM cte
WHERE cnt = (SELECT MAX(cnt) FROM cte);

If you also cannot rely on CTEs being available, you can easily enough just inline the CTE into the query. From a performance point of view, DENSE_RANK would probably outperform my answer.

With the CTE removed this becomes:

SELECT *
FROM
(
    SELECT p.id_category, COUNT(*) AS cnt
    FROM product p 
    INNER JOIN category c
        ON p.id_category = c.id_category
    GROUP BY p.id_category
)
WHERE cnt = (SELECT MAX(cnt) FROM (
                 SELECT p.id_category, COUNT(*) AS cnt
                 FROM product p 
                 INNER JOIN category c
                     ON p.id_category = c.id_category
                 GROUP BY p.id_category
            ));

This query would even run on MySQL. As you can see, the query is ugly, which is one reason why things like CTE and analytic functions were introduced into the ANSI standard.

Select TOP X (or bottom) percent for numeric values in MySQL

UPDATE: Much more thought-out explanation of the subject from much more knowing person here. Nonetheless, it still seems there's no embedded function in MySQL to calculate percentiles.

Try:

SELECT * FROM prices WHERE price >= (SELECT 0.9 * max(price) FROM prices)

SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

This will give price P1 for which number of records in Price table having price >= P1 will be one tenth of total number of records in Price table.
After that:

SELECT * FROM prices WHERE price >= (SELECT price FROM prices p1 WHERE
(SELECT count(*) FROM prices p2 WHERE p2.price >= p1.price) <=
     (SELECT 0.1 * count(*) FROM prices)
);

will return all desired records.

Note: I didn't examine performance of this query, I think solution with temporary table/variable must be more effective.

---

Related Topics