dslreports logo
 
    All Forums Hot Topics Gallery
spc
uniqs
26

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

Inssomniak to cdru

Premium Member

to cdru

Re: Need help with mysql command

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.