Operating System - HP-UX
1748104 Members
4597 Online
108758 Solutions
New Discussion юеВ

Re: SQL Teaser..easy points!!

 
SOLVED
Go to solution
Deepak Extross
Honored Contributor

SQL Teaser..easy points!!

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?
9 REPLIES 9
Josh Owings
Frequent Advisor

Re: SQL Teaser..easy points!!

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
Josh Owings
Frequent Advisor

Re: SQL Teaser..easy points!!

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

Re: SQL Teaser..easy points!!

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)
Ian Dennison_1
Honored Contributor

Re: SQL Teaser..easy points!!

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
Building a dumber user
Deepak Extross
Honored Contributor

Re: SQL Teaser..easy points!!

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.
Joseph C. Denman
Honored Contributor

Re: SQL Teaser..easy points!!

hummm....

how about:

select distinct c1,c2,c3 from tablename;

???????

...jcd...
If I had only read the instructions first??
Deepak Extross
Honored Contributor

Re: SQL Teaser..easy points!!

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")
Jonas Linden
New Member
Solution

Re: SQL Teaser..easy points!!

How about

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

/Jonas

Deepak Extross
Honored Contributor

Re: SQL Teaser..easy points!!

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