Select 2 separate columns in 4GL
Required for my 4G program:
Let's say I have a table that contains a phone number and a name. There can be 2 people with the same phone number or 2 names with 1 phone number.
I only need to select 1 from each phone number in the table.
I did:
SELECT DISTINCT phone_number, last_name FROM table
This will show 2 entries. Even the 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 also want to get the last name. I don't care which one)
source to share
DISTINCT
as you noticed will return lines that are completely different.
It looks like you are looking for something like group by
. Essentially, it GROUP BY phone_number
will return one line for each phone number. Since you also want to retrieve last_name
, you need to tell the database how you want to retrieve it. You said you didn't care so that you could just write:
SELECT phone_number, MAX(last_name) as last_name
FROM table
GROUP BY phone_number
Informix also supports the aggregate function FIRST_VALUE
, although I've only used it in OLAP situations, so I don't remember if it would work in that context.
source to share