|
In
Common SQL questions, part 1, we were introduced to
three types of common SQL questions. Having dispensed with the
not-so-serious types, which we rarely answer, it's time to
move on to the more serious and complex SQL questions.
3. Serious SQL questions
Not surprisingly, there are several common
if not "classic" types of complex SQL questions.
Duplicates
Finding "duplicates" is probably the #1 or #2 most common SQL question. The
scenario usually involves an autonumbering surrogate key,
with no additional unique constraint on the "real" (candidate) key.
- How I can extract the all the duplicate rows in a table?
- Hi sir, I want to delete duplicate data from the table
- How do I find duplicate entries in a table?
Lots of solutions exist, each involving a number of steps, many
of which are dictated by what you mean by "duplicates" and how you want
to handle disparities and missing data. So the solution must be tailored
to your specific scenario, and is of little use to other people.
Occasionally, though, the problem is tricky, and therefore interesting:
Top N
Often a popular homework question, the "Top N" problem also seems to
happen to everyone in real life.
- I need to display the first 'X' number of rows in a query.
- My select query comes back with 30,000 rows, and I just want the first 300.
- I need to get only 100 records from the bottom of the database based on specific field.
It is such a common question that we created a mini-FAQ for it years ago:
Top N for each X
More complex than simple Top N questions, but easy to recognize
because they invariably involve the word every or each.
Latest X for each Y
A subtle variation of the "Top N for each X" problem, involving dates:
As you can see, even though these are common questions, sometimes we answer them again.
Pagination
Sadly, pagination seems to give everyone trouble. Sometimes the best
solutions are implemented with caching, at the
application or middle tier level. Pagination with SQL is positively
fraught with difficulty.
Comma-delimited string questions
For some reason, denormalizing one-to-many data into comma-delimited strings
is a very common requirement:
Denormalizing on output isn't a sin, by the way. Storing
comma-delimited strings is a bad idea, though.
Miscellaneous questions
Finally, a few oddball questions that seem to come up often:
Remember, please do keep
sending in your questions. We love ’em.
|
