One of the most common questions is “how do I get select * from table, but without one of the column".
Short answer is of course – name your columns, instead of using *. Or use a view.
But I decided to take a look at the problem.
Continue reading Stupid tricks – hiding value of column in select *
Let's assume you have some simple database with “articles" – each article can be in many “categories". And now you want to get list of all articles in given set of categories.
Standard approach:
SELECT a.* FROM articles AS a JOIN articles_in_categories AS aic ON a.id = aic.article_id WHERE aic.category_id IN (14,62,70,53,138)
Will return duplicated article data if given article is in more than one from listed categories. How to remove redundant rows?
More than once I've seen situation when there is a table, with serial primary key, and rows contain also some kind of creation timestamp, which is usually monotonic, or close to monotonic.
Example of such case are for example comments or posts in forums – each get it's ID, but they also have creation timestamp. And it usually is so that higher ids were added later than the lower ids.
So, let's assume you have such table, and somebody asks you to make a report on data from last month. How?
Question from title sounds weird to you? It's just a ‘rm backup_filename'? Well. I really wish it was so simple in some cases.
One of the servers I'm looking into, there is interesting situation: