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

Select only the second of duplicates

All I want to do is print the second tuple where the NO is repeated:

 NO  NAME
 1   Name1
 2   Name2
 2   Name3
 2   Name4
 3   Name5
 3   Name6
 4   Name7
 5   Name8
 5   Name9

When I run my SQL query, my output must be --

 NO  NAME
 2   Name3
 3   Name6
 5   Name9

What is the query?


The first thing we need to establish is what you mean by the "second" tuple. There must be a sequence, a way of ordering the rows, that allows us to determine which one is first, and which one is second. By your example, I shall assume that "first" means the lowest collating NAME.

Now for the problem of finding the "second" one. There may be other ways to do it, but here's how I would approach this problem.

The "first" one is the lowest name, and we can get this with the MIN() function and grouping on NO:

select NO, min(NAME)
  from yourtable
group
    by NO

The "second" one is trickier. It is the lowest one that isn't the first one. But this time, instead of using GROUP BY, we use another method to produce grouping, the correlated subquery.

select NO, NAME
  from yourtable XX
 where NAME =
       ( select min(NAME)
           from yourtable
          where NO = XX.NO
            and NAME >
                ( select min(NAME)
                    from yourtable
                   where NO = XX.NO
                )
       )

To see how this works, consider any row in the outer query. Using the correlation variable XX to refer to this row in the outer query, the innermost subquery gets the lowest name for all rows with the same value of NO as the XX row being considered. This lowest value is used by the next outer subquery, which gets the lowest name that's greater than the lowest which was found by the innermost subquery. In other words, the second lowest. Then the outer query gets the row that has that second lowest name.

It's easy when you see it explained, but kind of hard to come up with on your own if you've never seen it before.

For More Information


This was last published in March 2003

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.

Start the conversation

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.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close