SQL King

21 Aug 2008 10:00

Are you an SQL King?

I once met a person that used to write SQL queries more sophisticated than this:

select *
    from category
    where category_id IN
        (select cat1 as category_id
            from (select min(category_id) as cat1, max(category_id) as cat2, name, site_id, count(*)
                from category
                group by name, site_id
                having count(*) > 1
            ) as c1
        union    select cat2 as category_id
            from (select min(category_id) as cat1, max(category_id) as cat2, name, site_id, count(*)
                from category
                group by name, site_id
                having count(*) > 1
            ) as c2
        );

This one is to check duplicated categories in sites from Wikidot. It's not optimal, but it works. Of course the situation when there exist duplicated categories is a bug, but it seems a really rare one, because from the beginning of Wikidot, there are only 20 such categories, all of them being forum, so this probably means a small bug in create a forum thing as well.

Previous post: Badge


More posts on this topic

Comments

Add a New Comment
or Sign in as Wikidot user
(will not be published)
- +
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License