dslreports logo
 
    All Forums Hot Topics Gallery
spc
uniqs
40

cdru
Go Colts
MVM
join:2003-05-14
Fort Wayne, IN

cdru to Inssomniak

MVM

to Inssomniak

Re: Need help with mysql command

Presuming id is unique and always incrementing, we need to first get the IDs for the highest unique usernames:
SELECT MAX(id)
FROM your_table_name
GROUP BY username
 

If you need to determine uniqueness based on additional columns (e.g. username and contact), add the additional columns to the Group By statement.

Once we have all the unique columns, we can negate the above, selecting the rows that don't match the columns we selected above:
SELECT * FROM your_table_name
WHERE id NOT IN (
   SELECT * 
   FROM (
       SELECT MAX(id)
       FROM your_table_name
       GROUP BY username
   )
)
 

Again, test that this is actually selecting the rows you want to delete, then change the SELECT * to DELETE. No guarantees on the correctness of my statements so hopefully you aren't using this on a production system or at least one you can't afford to delete something you shouldn't have.

Inssomniak
The Glitch
Premium Member
join:2005-04-06
Cayuga, ON

Inssomniak

Premium Member

Wow you are good

Seems to do the trick just the way it is .
Thanks so much!
Inssomniak

Inssomniak to cdru

Premium Member

to cdru
Hey again. I just got around to trying to use this command to DELETE, but I got an error. It seems to work perfectly for the SELECT. Any ideas?

 
root@sip:~# mysql kamailio -u root  -e "DELETE FROM location WHERE id NOT IN (SELECT MAX(id) FROM location GROUP BY username)"
ERROR 1093 (HY000) at line 1: You can't specify target table 'location' for update in FROM clause
root@sip:~# 
 

cdru
Go Colts
MVM
join:2003-05-14
Fort Wayne, IN

cdru

MVM

You may need to wrap the nested select inside another select to create a derived table, breaking the direct relationship of "location" of the subquery from that of the delete. Try the delete on the text I gave in the 2nd example.

Inssomniak
The Glitch
Premium Member
join:2005-04-06
Cayuga, ON

Inssomniak

Premium Member

I get a new error below:

 
root@sip:~# mysql kamailio -u root  -e "SELECT * FROM location WHERE id NOT IN (SELECT * FROM (SELECT MAX(id) FROM location GROUP BY username ))"
ERROR 1248 (42000) at line 1: Every derived table must have its own alias
 

cdru
Go Colts
MVM
join:2003-05-14
Fort Wayne, IN

cdru

MVM

try
DELETE FROM your_table_name
WHERE id NOT IN (
   SELECT *
   FROM (
       SELECT MAX(id)
       FROM your_table_name
       GROUP BY username
   ) t
)
 

Inssomniak
The Glitch
Premium Member
join:2005-04-06
Cayuga, ON

Inssomniak

Premium Member

Perfect!.. Thanks so much!
Inssomniak

Inssomniak to cdru

Premium Member

to cdru
I really hate digging up this thread, but I ran into a new problem once I registered more than one ATA to an account, it was deleting a valid entry.

I still need it to delete all but the newest entry based on username, (ID being the newest), but also with the same cseq entry.

So if I have 5 entries with the same username, and 3 of those have the same cseq, and 2 of them have a different cseq, I need it to keep the newest unique cseq for the username.

This will allow it to keep the latest entry for each ATA registered.

Im not sure if that made sense.

Thanks for any help

cdru
Go Colts
MVM
join:2003-05-14
Fort Wayne, IN

cdru

MVM

said by Inssomniak:

I still need it to delete all but the newest entry based on username, (ID being the newest), but also with the same cseq entry.

Already anticipated and answered in the 5th post of this thread.