topic Re: SQL Teaser..easy points!! in Operating System - HP-UX

SQL Teaser..easy points!!

Deepak Extross — Tue, 27 Nov 2001 02:29:18 GMT

I have a table with say 4 columns C1, C2, C3 and C4.
Many records have identical values of C1, C2 and C3, but C4 may be different in each row. I want to do a "select unique C1, C2, C3 and any one C4, doesnt matter which one".

For example, lets say my table has:
C1 C2 C3 C4
a b c d
a b c r
a d c p
q w e r
q w e g
q w e g

I should get only 2 rows:
a b c d/r/p
q w e r/g

Any guesses?

Re: SQL Teaser..easy points!!

Josh Owings — Tue, 27 Nov 2001 03:08:12 GMT

select * from (table)
group by C1,C2,C3
order by C1,C2,C3

would be what I would do, but I would get more than two rows.

C1 C2 C3 C4
-----------
a b c d
a b c r
a b c p
q w e r
q w e g

Re: SQL Teaser..easy points!!

Josh Owings — Tue, 27 Nov 2001 03:11:08 GMT

Correction. my sample output is incorrect given the "order by" statement, but the point was made.

Re: SQL Teaser..easy points!!

Deepak Extross — Tue, 27 Nov 2001 03:18:01 GMT

Thanks, but the requirement is to retrieve only 2 rows.
(with the distinct C1, C2 and C3).
Besides, I get an error saying that C4 must be listed in the group-by clause. (Informix Dynamic Server Version 7.31)

Re: SQL Teaser..easy points!!

Ian Dennison_1 — Tue, 27 Nov 2001 11:22:28 GMT

I'm a bit rusty on my SQL, but I think you are looking for a function that take a list of rows and builds it into a single column / field.

Have you looked for something like a 'crosstab' concept or using PL/SQL to programmatically create this?

What database package are you using?

Share and enjoy! Ian

Re: SQL Teaser..easy points!!

Deepak Extross — Tue, 27 Nov 2001 14:11:35 GMT

I'm using Informix (IDS v7.31).
Well, I'm looking to this using pure SQL - even if it involves temp tables, outer joins and other such niceties.

Re: SQL Teaser..easy points!!

Joseph C. Denman — Tue, 27 Nov 2001 22:26:54 GMT

hummm....

how about:

select distinct c1,c2,c3 from tablename;

???????

...jcd...

Re: SQL Teaser..easy points!!

Deepak Extross — Wed, 28 Nov 2001 02:10:17 GMT

Thanks jcd, but I need any one c4 as well.
(It should be one of the valid c4's. Otherwise I would have just done a "select unique c1, c2, c3, dummy as c4 from tablename")

Re: SQL Teaser..easy points!!

Jonas Linden — Wed, 28 Nov 2001 15:38:50 GMT

How about

select C1,C2,C3,max(C4)
from tablename
group by C1,C2,C3;

/Jonas

Re: SQL Teaser..easy points!!

Deepak Extross — Thu, 29 Nov 2001 03:19:29 GMT

Brilliant!
Jonas, you have me kicking myself for not thinking of that earlier.
Thanks a ton to all who chipped in.