Getting unique results based on a specific combination of table columns?

Question

I'm having some issues returning unique results from a table that has some repeating values. The table I am using is as follows:

Venue ID  | Venue   | Venue Phone |  Venue Address| 
10659      Ottawa
10587      Toronto
10967      Ottawa
10492      Hamilton
10595      Hamilton

I need to display the venue name proper, but return the venue id (I can only use two columns as I working with a List of Values in Oracle Apex). The issue is that venue names can repeat, but venue id's are always unique. So using the queries I've been using for other lists, I will end up with duplicate venue names. Is there any query that can achieve what I want?

What would you want to appear in the list of values. If there are two different "Ottowa" venues, for example, how would a user selecting from the list of values know which "Ottowa" they wanted? You could append some other data to make the string unique but you'd need to tell us what additional data you have that would be meaningful to your users that would uniquely identify a venue. — Justin Cave
– Justin Cave, Commented Feb 10, 2012 at 17:55
Doesn't matter which venue they end up choosing, as long as it has a Venue ID. So, even though there were two Ottawa's, the only one that would ever be used would be the one that would ideally appear on the select list. It's not ideal, I know, but unfortunately I cannot change the DB. — user1200142
– user1200142, Commented Feb 10, 2012 at 18:01

Justin Cave · Accepted Answer · 2012-02-10 18:03:23Z

1

If you don't care which of the venue ID's is picked, you could simply do a GROUP BY and pick the one with the larger VENUE_ID

SELECT max(venue_id) venue_id, venue venue_name
  FROM table_name
 GROUP BY venue

answered Feb 10, 2012 at 18:03

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

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

1 Comment

user1200142 Over a year ago

I don't know why I never thought of that! That works perfectly. Thanks very much, appreciate it!

Alex Peta · Accepted Answer · 2012-02-14 18:18:22Z

1

You can also use windows function, for instance

SELECT * 
  FROM (
        SELECT venue_id, 
               venue 
               --other fields
               ROW_NUMBER() OVER (ORDER BY venue_id PARTITION BY venue) AS num
          FROM table_name
       )a
WHERE num=1

edited Feb 14, 2012 at 18:18

Alex Peta

1,4051 gold badge15 silver badges27 bronze badges

answered Feb 10, 2012 at 18:08

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Comments

cagcowboy · Accepted Answer · 2012-02-10 18:04:16Z

0

SELECT MIN(ID), VENUE
FROM   <table>
GROUP BY VENUE

Would give you...

Venue ID  | Venue   | More columns 
10659      Ottawa
10587      Toronto
10967      Ottawa
10492      Hamilton

Not sure if this is what you're after?

answered Feb 10, 2012 at 18:04

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

2 Comments

user1200142 Over a year ago

Opposite in terms of max-min to what Justin posted, but same idea, so I'm assuming this would have worked just as fine.

cagcowboy Over a year ago

Using MIN has the marginal advantage that if you add another row later with the same venue, you keep referencing the same id (assuming the column is a NUMBER column)

Collectives™ on Stack Overflow

Getting unique results based on a specific combination of table columns?

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related