Misadventures in SQL

Let's say you have a database full of links, and you want to know how many unique links you have (unique meaning a unique URL). In MySQL, you can write:

SELECT COUNT(DISTINCT(url)) AS num FROM links);

and it works fine. However, SQLite will barf.

Googling a bit, I found a few examples of doing this in SQLite, usually something like:

SELECT COUNT(url) FROM (SELECT DISTINCT url FROM links);

Works great in SQLite, but now MySQL will barf.

I found this works in both:

SELECT COUNT(url) FROM (SELECT DISTINCT url FROM links) t1;

The "t1" at the end allows MySQL to alias the subselect (which is required), while SQLite (which doesn't require the aliasing) will chug along just fine.

Permalink • Posted in: tech stuff, sql • Post a comment