Problem solve Get help with specific problems with your technologies, process and projects.

SQL FAQ: Common SQL questions, part 3

This three-part answer addresses the most frequently asked SQL questions. Part three looks at the more complex common questions.

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.


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.


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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.