OS and Software > Webmasters and Developers > Need help with mysql command

reply to cdru

Re: Need help with mysql command

 
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
 

try

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

Perfect!.. Thanks so much!

reply 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
--
OptionsDSL Wireless Internet
»www.optionsdsl.ca