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ᴇᴌᴀ
  • 7,491
  • 8
  • 27
  • 56
Moses Davidowitz
  • 958
  • 11
  • 24

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,278
  • 7
  • 21
  • 49
Code Magician
  • 21,032
  • 5
  • 55
  • 75
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
  • 4,619
  • 1
  • 12
  • 22