Need to do rank in mysql workbench

Question

1.00/5 (2 votes)

See more:

I need to remove duplication records from one table, so need to do rank for it.
But version 8.0 does not support the row_number() function for it.

What I have tried:

Below query i have try for it

MySQL

IF (
@prev = column1,
@rn := @rn + 1,
CASE WHEN (@prev := column1) IS NOT NULL OR TRUE THEN @rn := 1 END
) AS rnk

select query

(SELECT @rn := 0, @prev := NULL) r

But it is taking time and not providing accurate result for it.

Posted 15-Aug-24 9:13am

Member 11776570

Updated 16-Aug-24 5:03am

Andre Oosthuizen

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2024-08-15T22:33:00

You haven't shown us your table structure so we can't really answer this for you but how are you defining a record as a duplicate? Are you just looking at one column or multiple? Let's assume that what you are really looking at is a duplicate record is one where one column contains the same value, as this is a simple one to demonstrate, and the same principle applies to multiple columns as well. What you want to do is to do a join on the same table, to look for rows where there is more than one value. I'm going to demonstrate what the select might look like for this column (I'll come to why this is, in a minute)

SQL

select * from myFunkyTable where 
id not in (select min(id) from myFunkyTable group by myPotentiallyDuplicateColumn having count(*) > 1)
and myPotentiallyDuplicateColumn in (select myPotentiallyDuplicateColumn from myFunkyTable group by myPotentiallyDuplicateColumn having count(*) > 1)

What I'm doing in this query is pulling all the potentially duplicated records back based on the assumption that you are using a sequential id, so we're looking for every record that is greater than the minimum id.

So, why are we doing a select first? That's to get you ready for the fact that you also need to consider tables that might be pointing to this table. If you have references back to "myFunkyTable", then you have to remember that you could end up breaking things if you start deleting records. If you have related tables, then you will want to do an update on the other tables to point the records to the minimum id record.