2

I'm trying to figure out how pull select values from a table column, but return other results as well. For example say I have the following table

person  ..........
------------------
bob     ..........
mary    ..........
bob     ..........
sue     ..........

What I want to return back is

bob     ..........
mary    ..........
sue     ..........

In this case I don't care what values I am actually receiving back for bob from the other columns how would I do that?

Follow up question, what if I did care though? What if I had this table

person  type    color   ..........
----------------------------------
bob     0       blue    ..........
mary    1       green   ..........
bob     1       red     ..........
sue     0       yellow  ..........

And this time I again want unique people, but I want the results back for bob when his type is 0, so for him I would see

bob     0   blue    ..........
mary    1   green   ..........
sue     0   yellow  ..........

Thanks!

Improve this question

2 Answers 2

Reset to default
8

You can use analytic functions like RANK to describe the algorithm for which row you want to keep and then do something like

SELECT *
  FROM (SELECT person,
               type,
               color,
               RANK() OVER( PARTITION BY person
                                ORDER BY type asc ) rnk
          FROM <<person_table>>)
 WHERE rnk = 1

This returns the "first" row for each person where "first" is determined by the row with the lowest TYPE value.

One thing to be aware of when you start looking at ranking rows is that there are three different analytic functions-- RANK, DENSE_RANK, and ROW_NUMBER-- that all do basically the same thing but handle ties differently. RANK is the standard athletics ranking function-- if there are two rows that tie for first place (i.e. two rows where PERSON='bob' and TYPE=0), they would both get a rank of 1 while the next row would get a rank of 3 so there would be no "second place" row. In the same situation, DENSE_RANK would also give both tied rows a rank of 1 but would give the next row a rank of 2. ROW_NUMBER would arbitrarily give one of the tied rows a rank of 1, give the other one a rank of 2, and give the third row a rank of 3. Of course, you can break ties by adding additional columns to the ORDER BY clause.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@Justin +1 for answering the harder 2nd question. The 1st question is use DISTINCT (which oddly is in the tag)
@Conrad - I didn't interpret this as being two different questions, though you're right that it's a little unclear. The first question wouldn't use DISTINCT because the other columns for the two BOB rows may not be exact matches. I interpret the first part of the question to be "I don't care which of the two BOB rows are returned" which would be the same query just with changes to the analytic function. If they really don't care which row is returned, the solution of returning the row with the smallest TYPE would seem to be a valid answer to both parts of the question.
@Justin - Thank you! I need to go read up on the Rank(), Over( Partition ...) code =)
@Conrad - Justin was correct in that the other columns would have caused issues with Distinct, he had it figured out though
@dscl - Also take a look at the DENSE_RANK and ROW_NUMBER analytic functions. Those three are all very similar but they handle ties differently.
1

Alternative syntax using Generic SQL for DBs that don't have RANK

SELECT
       person,
       type,
       color

FROM 

   <<person_table>> p 
   inner join 
  (SELECT person, 
         min(type) type              
    FROM <<person_table>>
    GROUP BY 
         person) minType
    on p.Person = minType.Person
       and p.type = minType.type
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.