Mysql's Alternative to T-Sql's with Ties

MySQL's alternative to T-SQL's WITH TIES

Does this work for you?

select Name, Value from table where Value in (
    select distinct Value from table order by Value desc limit 3
) order by Value desc

Or perhaps:

select a.Name, a.Value 
from table a
join (select distinct Value from table order by Value desc limit 3) b
     on a.Value = b.Value

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.

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

Select Top N rows and also handle tie situation

Use a sub-query to get the third highest cost:

select * from table
where cost >= (SELECT COST FROM TABLE
               ORDER BY COST DESC
               LIMIT 3,1)
ORDER BY c_cost DESC

(I'm not fully sure about the LIMIT 3,1 part, since I'm not a MySQL guy. Test and see!)

Trying to get the 2 min(count()) with Ties

View is not required, a query result could be used as subquery

SELECT 
  COUNT(*) NbrProposedDate, 
  MAX(f.FIRST_NAME) FIRST_NAME,
  MAX(f.LAST_NAME) LAST_NAME, 
  f.FRIEND_ID
FROM PROPOSES
NATURAL JOIN FRIEND f
GROUP BY f.FRIEND_ID
HAVING COUNT(*) = (
  SELECT COUNT(*) 
  FROM PROPOSES
  NATURAL JOIN FRIEND f
  GROUP BY f.FRIEND_ID
  ORDER BY COUNT(*)
  LIMIT 1
)

On MySQL 8+, it can also use RANK()

WITH ranks AS (
SELECT 
  COUNT(*) NbrProposedDate, 
  MAX(f.FIRST_NAME) FIRST_NAME,
  MAX(f.LAST_NAME) LAST_NAME, 
  f.FRIEND_ID,
  RANK() OVER (ORDER BY COUNT(*) ASC) rk
FROM PROPOSES
NATURAL JOIN FRIEND f
GROUP BY f.FRIEND_ID
)
SELECT * FROM ranks WHERE rk = 1

SQL query with Highest value with tie

Use RANK() window function:

SELECT t.dept_name, t.name, t.tot_cred
FROM (
  SELECT dept_name, name, tot_cred, 
    RANK() OVER(PARTITION BY dept_name ORDER BY tot_cred DESC) rn
  FROM university.student 
) t
WHERE t.rn = 1

This is an alternative in case you can't use window functions:

SELECT s.dept_name, s.name, s.tot_cred
FROM university.student s
INNER JOIN (
  SELECT dept_name, MAX(tot_cred) tot_cred
  FROM university.student
  GROUP BY dept_name
) t ON t.dept_name = s.dept_name AND s.tot_cred = t.tot_cred

or with NOT EXISTS:

SELECT s.dept_name, s.name, s.tot_cred 
FROM university.student s 
WHERE NOT EXISTS (
  SELECT 1 FROM university.student
  WHERE dept_name = s.dept_name AND tot_cred > s.tot_cred  
)

Fetch first N rows including tie values MYSQL

You will have to perform an INNER JOIN, using the table back on itself. First, you want to select the top 3 unique/distinct scores, and this can be done by using:

SELECT DISTINCT Votes FROM mytable ORDER BY Votes DESC LIMIT 3

Now that you have obtained the top 3 scores, you want to join it back to the original table:

SELECT t1.* FROM mytable AS t1
INNER JOIN
  (SELECT DISTINCT Votes FROM mytable ORDER BY Votes DESC LIMIT 3) AS topvotes
ON
  topvotes.Votes = t1.Votes
ORDER BY t1.Votes DESC

Refer to a simple diagram for the strategy:

For this query to be efficient, you will want to index the Votes column so that the subquery can fish out distinct votes quickly ;)

Here is a proof-of-concept SQLfiddle: http://sqlfiddle.com/#!9/c78f0/10

Alternative to using GROUP BY without aggregates to retrieve distinct best result

This is basically a form of the groupwise-maximum-with-ties problem. I don't think there is a SQL standard compliant solution. A solution like this would perform nicely:

SELECT  s2.id
,       s2.title
,       s2.episode
,       s2.is_hidef
,       s2.is_verified
FROM    (
        select  distinct title
        ,       episode
        from    shows
        where   title = 'The Simpsons' 
        ) s1
JOIN    shows s2
ON      s2.id = 
        (
        select  id
        from    shows s3
        where   s3.title = s1.title
                and s3.episode = s1.episode
        order by
                s3.is_hidef DESC
        ,       s3.is_verified DESC
        limit   1
        )

But given the cost of readability, I would stick with your original query.

How to handle ties in SQL

You can use a not exists query which gives you more flexibility:

SELECT *
FROM employees e
WHERE NOT EXISTS (                                                  -- no x exists that...
    SELECT *
    FROM employees x
    WHERE x.department_id = e.department_id                         -- belongs to same department 
    AND (
        x.hire_date > e.hire_date OR                                -- but hired later than e
        x.hire_date = e.hire_date AND x.employee_id < e.employee_id -- hired on same date but has lesser employee id than e
    )
)

---

Related Topics