Q
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.

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 last published in July 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

I had no idea that you could even do a COUNT(1)! SQL has a lot of little tricks that don't become obvious until you need them. I like this sort of list that exposes me to questions that I had not thought to ask because I didn't have a need.  The top 5 query without using TOP was also fairly interesting solution, however it states it was an oracle DB and therefore could have used ROWNUM which was not one of the answers given.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close