Requires Free Membership to View

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at [email protected].

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.

This was first published in July 2007

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.