dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
20
share rss forum feed


Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
reply 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
 

--
OptionsDSL Wireless Internet
»www.optionsdsl.ca


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

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
join:2005-04-06
Cayuga, ON
kudos:2

Perfect!.. Thanks so much!



Inssomniak
The Glitch
Premium
join:2005-04-06
Cayuga, ON
kudos:2
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



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

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.