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?
LVL 1
nickrawlinsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.