Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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
Occasional Visitor
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.