CODEWITHSUNDEEP
sqlinformix
ChristianNRW
ChristianNRW

Reputation: 542

GROUP BY in Informix (11.5)

Following Example Table Structure:

NR1 | NR2 | PRENAME | LASTNAME

If i query all 4 fields of this table and grouping it´s first 2 fields (NR1,NR2) in mysql, i can do something like this:

SELECT NR1,NR2,PRENAME,LASTNAME FROM tbl GROUP BY NR1,NR1

But this won´t work in informix.

INFORMIX ERROR: the column (PRENAME) must be in the group by list

After reading some Topics at google, it is an "Informix feature" that all Selected Columns has to be in the Grouping List. But if i will do that, the result is not that result, that i wish to have. If i use 

DISTINCT

instead GROUP BY the result is similar false, because i can not put the DISTINCTfunction only to column 1 and 2.

So: How can i make a "MYSQL GROUP BY" function ?

Upvotes: 1

Views: 2916

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Your original syntax is suitable in one database -- MySQL. And, that database says that the results of the non-aggregated columns come from indeterminate rows. So, an equivalent query is just to use MIN() or MAX():

SELECT NR1, NR2, MIN(PRENAME), MIN(LASTNAME)
FROM tbl
GROUP BY NR1, NR1;

My guess is that you want an arbitrary value from just one row. I'd be inclined to concatenate them:

SELECT NR1, NR2, MIN(PRENAME || ' ' || LASTNAME)
FROM tbl
GROUP BY NR1, NR1;

Upvotes: 1

Related Questions