On May 31, 9:40 am, Yvon Thoraval <[email protected]> wrote:
> Hey all,
>
> i have to find duplicates item, by name, in a table, for clarity the items
> represent an application by its name, and its path.
>
> if i do :
>
> sql = DB[:applications].group_by(:name).having{|o| o.count(:name) >
> 1}.order(:name).sql
>
> i get :
>
> sql = SELECT * FROM `applications` GROUP BY `name` HAVING (count(`name`) >
> 1) ORDER BY `name`
>
> and executing that gave me only one example of the duplicates, for exampe :
>
> # -> a[:name] = Assistant.app (a[:path] =
> /Applications/MacPorts/Qt/Assistant.app)
>
> is the only one i've found by the above.
>
> but not the one it duplicates :
>
> # -> a[:name] = Assistant.app (a[:path] =
> /Developer/Applications//Qt/Assistant.app)
>
> ie same name different pathes.
>
> i don't understand why the second isn't listed by this first sql.
First, you are using non-standard SQL. You generally can't select *
and group by a column, as all columns selected must either be grouped
by or used in a aggregate function. I'm guessing you are using MySQL,
which allows this nonstandard behavior. You are only going to get one
row for each name because you are grouping on name. This should do
what you want:
DB[:applications].filter(:name=>DB[:applications].select(:name).group_by(:name).having{|
o| o.count(:name) > 1}).order(:name)
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
