• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 707
  • Last Modified:

Select distinct for one column only?

Hi,

How can I select a distinct value for only one columb but retrieve all my other columns?

Basically, I have this data:

Sequence, Name, Code, Value, PURCHYN
1, TestName, TestCode, TestValue, TestPurchyn
2, OtherName, OtherCode, OtherValue, OtherPurchyn
3, TestName, TestCode, TestValue, TestPurchyn

I want to retrive all fields from the above but do a distinct on Code and return the highest sequence of the duplicates...

This is in Oracle...

Any advise please?
0
nickrawlins
Asked:
nickrawlins
2 Solutions
 
sdstuberCommented:
I'm sorry, I don't quite understand the description.
What are you expecting the final result to look like for the above data?
0
 
cataleptic_stateCommented:
SELECT DISTINCT field, * FROM table
0
 
sdstuberCommented:
I think "maybe" this is what you are looking for


SELECT sequence, name, code, VALUE, purchyn
FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY code ORDER BY sequence DESC) rn
      FROM yourtable t)
WHERE rn = 1
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
slightwv (䄆 Netminder) Commented:
>>but do a distinct on Code and return the highest sequence of the duplicates...

I'm with sdstuber, I'm not sure I understand but if I guess a little are you wanting the count of duplicate codes?

select code, count(1) from table group by code having count(1) > 1;
0
 
sdstuberCommented:
cataleptic_state,

SELECT DISTINCT field, * FROM table

won't work,  first,  you can't use "*" with other fields, you need an alias for it.

Second, since the sequence column appears to be unique, doing a DISTINCT on "field" and all other columns will simply return every row since each row is already distinct by its sequence.
0
 
nickrawlinsAuthor Commented:
Sorry, I have resolved the issue:

SELECT  max(sequence), code as CODE, name as NAME, PURCHYN, SRVYN
FROM       table group by code, name, purchyn, srvyn

I didn't realise you can exclude the sequence from the group by!

Thanks,

Nick
0
 
sdstuberCommented:
my query above returns one row for each CODE, the row returned will be the one with the highest sequence for that CODE

so, in the example above,  I would return two rows...

sequence 2 for "OtherCode" and sequence 3 for "TestCode"
0
 
sdstuberCommented:
nickrawlins,

are you sure you want to group by ALL other columns? I thought you were looking for distinct CODE's

From your example data, your query would return the same thing my query did

but what if sequence 1 had purchyn of "NickPurchyn" and sequence 3 had "TestPurchyn"

what would you want returned? Your query will return all 3 rows because each is a unique grouping, mine will return the same 2 rows I detailed above.

0
 
sdstuberCommented:
Thanks Vee_Mod!

nickrawlins,

If you DO want to find distinct combinations of all columns except the sequence then your query above with the group by is correct.
I only objected because I wanted to make sure you got the answer you really wanted, not just one that happened to work for a specific test case of data.
0
 
awking00Commented:
The use of max(sequence) only works on nickrawlins' sample data because the name, value, and purchyn also have duplicate values for sequences 1 and 3. If he truly wants to return rows based on the distinctness of the code column, then sdstuber's solution is the way to go (it can also be done with self joins but the analytical method is by far the better way to do this).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now