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:
How to get a list of employees who have the same last and first name?
Employees with the same first and last names in SQL (11 May, 2007)
Accidentally the key got dropped and some duplicate records were inserted into the table....
Removing duplicate rows (9 February, 2005)
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:
- FIRST N rows, TOP N rows, LAST N rows, BOTTOM N rows... (25 November 2002)
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.
How will I find the first 5 highest salaried employees in each dept?
How to find the first 5 highest salaried employees in each department (25 April, 2001)
Top 5 salaried employees, not using TOP (13 July, 2001)
I want a query that will give me the top ten sales for each salesman.
Top ten sales for each salesman (17 May, 2004)
How do I retrieve the top 10 brands(sorted by units) for each category-size combination?
Top N rows for each X (21 March, 2005)
Latest X for each Y
A subtle variation of the "Top N for each X" problem, involving dates:
... the most current wage for each employee.
Rows having maximum group value in MySQL (15 November 2002)
... only latest record from each group.
Latest row for each group (17 December 2003)
... three latest payments for each account.
Latest three payments for each account (13 April, 2007)
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.
Paging through a result set with SQL (16 July 2002)
Paging through SQL query results (14 February 2005)
Paging through a result set without TOP or LIMIT (24 October 2002)
Comma-delimited string questions
For some reason, denormalizing one-to-many data into comma-delimited strings is a very common requirement:
Concatenate into a single row (28 June 2006)
Concatenate values into comma-delimited string (14 February 2005)
Denormalizing on output isn't a sin, by the way. Storing comma-delimited strings is a bad idea, though.
Finally, a few oddball questions that seem to come up often:
More about ORDER BY for a specified sequence (13 March, 2007)
COUNT(*) or COUNT(1) (17 January, 2007)
Two "copies" of a lookup table in the same query (17 May 2004)
The "any" option in dynamic search SQL (8 April 2003)
Remember, please do keep sending in your questions. We love ’em.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading