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)
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.