Searching next DUPLICATE entry in MYSQL?

[Jarrod]

If I have a table that is like the following:

RECORDINDEX NAME

the record index is incremented with each value, however, their may be duplicate NAMES, however with different RECORDINDEX because of the increment. Is there a way to go to the NEXT duplicate with a bigger recordindex?

Hard to describe what I mean, I've pondered about it but I can't figure it out. I'm new to SQL.

Thanks in advance

 

[Redrum]

I'm not sure I follow what you're trying to do. So RECORDINDEX is an auto-increment column, and you have a record and you want to find the record with the next highest RECORDINDEX with the same NAME?

You can use min. So say the record you have has a RECORDINDEX of 5, you could do:

select min(RECORDINDEX) from table_name where NAME="the name you're looking for" and RECORDINDEX > 5

or you can use limit, like so:

select * from table_name where NAME="the name you're looking for" and RECORDINDEX > 5 order by RECORDINDEX limit 1

But the way you use the term duplicate, it makes me wonder if there are supposed to be any duplicates in the first place. If there isn't, you should fix the code that made the duplicates, tidy up the data then name NAME a unique column in MySQL.

 

[Jarrod]

I'm not sure I follow what you're trying to do. So RECORDINDEX is an auto-increment column, and you have a record and you want to find the record with the next highest RECORDINDEX with the same NAME?

You can use min. So say the record you have has a RECORDINDEX of 5, you could do:

select min(RECORDINDEX) from table_name where NAME="the name you're looking for" and RECORDINDEX > 5

or you can use limit, like so:

select * from table_name where NAME="the name you're looking for" and RECORDINDEX > 5 order by RECORDINDEX limit 1

But the way you use the term duplicate, it makes me wonder if there are supposed to be any duplicates in the first place. If there isn't, you should fix the code that made the duplicates, tidy up the data then name NAME a unique column in MySQL.

That first bit of code is exactly what I needed, thanks man really appreciate it. There are going to be duplicates due to the nature of the program Thanks a lot man