3

Needed for my 4gl program:
Let's say I have a table that holds a phone number and a name. There can be 2 people with the same phone number, or 2 names with 1 phone number.
I need to select just 1 of each phone number in the table.
I did:

SELECT DISTINCT phone_number, last_name FROM table

The results will show 2 records. Even phone number is the same, since the names are different it is no longer unique. How can I get a unique phone number regardless of its last_name? (But I want to get the last name as well. I don't care which one)

Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
Moses Davidowitz
  • 982
  • 11
  • 28

2 Answers2

5

DISTINCT, as you've noticed, will return rows that are distinct in their entirety.

It sounds like you're looking for something like group by. Essentially, GROUP BY phone_number will return one row for each phone number. Because you also want to get last_name, you'll need to instruct the database how you want it to be returned. You said you don't care which so you could simply write:

SELECT phone_number, MAX(last_name) as last_name
FROM table
GROUP BY phone_number

Informix also supports a FIRST_VALUE aggregate function although I've only used that in OLAP situations so I don't recall if it will work in this context.

Winter
  • 3,894
  • 7
  • 24
  • 56
Code Magician
  • 23,217
  • 7
  • 60
  • 77
3

If you don't care which last name, then try this out:

SELECT phone_number,
       MAX(last_name) AS last_name
FROM table
GROUP BY phone_number
Stephan
  • 5,891
  • 1
  • 16
  • 24