Group by or distinct URGENT !
| Author |
Message |
|
Osca
#1 / 5
|
 Group by or distinct URGENT ! Hi everyonem, how can I do the next: T is a TABLE
A B C D E F ....
2 1 10 15 10 2
2 2 10 15 10 3
2 3 12 13 3 4
2 4 17 17 3 5
2 5 19 5 8 6
2 6 19 5 8 1
2 7 20 9 12 2
I need the result: with C,D,E DIFFERENTS !
A B C D E F ....
2 1 10 15 10 2
2 3 12 13 3 4
2 4 17 17 3 5
2 5 19 5 8 6
2 7 20 9 12 2
If I do select C,D,E from T group by C,D,E --> it's OK but i need a select
with all the rows !!!
something like: select * fromt T grupo by C,D,E
Is it possible ? Thank's, OSCAR
|
| Mon, 21 Feb 2005 15:17:34 GMT |
|
 |
|
Richard Foot
#2 / 5
|
Group by or distinct URGENT ! Hi Oscar, If you want to select distinct C, D, E including details of the other
columns, which particular values of the other columns are you after ?
Your request doesn't make sense in that by summarising the data based on a
subset of columns, it's not possible to then display the numerous
occurrences of the other columns.
Unless I'm totally misunderstanding your request ...
Cheers
Richard
Quote: > Hi everyonem, how can I do the next: > T is a TABLE
> A B C D E F ....
> 2 1 10 15 10 2
> 2 2 10 15 10 3
> 2 3 12 13 3 4
> 2 4 17 17 3 5
> 2 5 19 5 8 6
> 2 6 19 5 8 1
> 2 7 20 9 12 2
> I need the result: with C,D,E DIFFERENTS !
> A B C D E F ....
> 2 1 10 15 10 2
> 2 3 12 13 3 4
> 2 4 17 17 3 5
> 2 5 19 5 8 6
> 2 7 20 9 12 2
> If I do select C,D,E from T group by C,D,E --> it's OK but i need a select
> with all the rows !!!
> something like: select * fromt T grupo by C,D,E
> Is it possible ? Thank's, OSCAR
|
| Mon, 21 Feb 2005 15:42:37 GMT |
|
|
|
Osca
#3 / 5
|
Group by or distinct URGENT ! It doesn't cara about the value of the other registers. But I need them for fill a table, I know it seems not to have a sense but it's for an adaptation of a query with DB400. Help !
Quote: > Hi Oscar, > If you want to select distinct C, D, E including details of the other
> columns, which particular values of the other columns are you after ?
> Your request doesn't make sense in that by summarising the data based on a
> subset of columns, it's not possible to then display the numerous
> occurrences of the other columns.
> Unless I'm totally misunderstanding your request ...
> Cheers
> Richard
> > Hi everyonem, how can I do the next:
> > T is a TABLE
> > A B C D E F ....
> > 2 1 10 15 10 2
> > 2 2 10 15 10 3
> > 2 3 12 13 3 4
> > 2 4 17 17 3 5
> > 2 5 19 5 8 6
> > 2 6 19 5 8 1
> > 2 7 20 9 12 2
> > I need the result: with C,D,E DIFFERENTS !
> > A B C D E F ....
> > 2 1 10 15 10 2
> > 2 3 12 13 3 4
> > 2 4 17 17 3 5
> > 2 5 19 5 8 6
> > 2 7 20 9 12 2
> > If I do select C,D,E from T group by C,D,E --> it's OK but i need a
select
> > with all the rows !!!
> > something like: select * fromt T grupo by C,D,E
> > Is it possible ? Thank's, OSCAR
|
| Mon, 21 Feb 2005 15:54:41 GMT |
|
|
|
TurkBea
#4 / 5
|
Group by or distinct URGENT ! Perhaps you could use separate queries ( one using the distinct clause on C.D,E and one just 'plain' select on A,B,F ) and UNION them... Quote:
>Hi everyonem, how can I do the next: >T is a TABLE
>A B C D E F ....
>2 1 10 15 10 2
>2 2 10 15 10 3
>2 3 12 13 3 4
>2 4 17 17 3 5
>2 5 19 5 8 6
>2 6 19 5 8 1
>2 7 20 9 12 2
>I need the result: with C,D,E DIFFERENTS !
>A B C D E F ....
>2 1 10 15 10 2
>2 3 12 13 3 4
>2 4 17 17 3 5
>2 5 19 5 8 6
>2 7 20 9 12 2
>If I do select C,D,E from T group by C,D,E --> it's OK but i need a select
>with all the rows !!!
>something like: select * fromt T grupo by C,D,E
> Is it possible ? Thank's, OSCAR
-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==---------- http://www.newsfeed.com The #1 Newsgroup Service in the World! -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
|
| Mon, 21 Feb 2005 21:39:44 GMT |
|
|
|
Jusung Ya
#5 / 5
|
Group by or distinct URGENT ! If I understand your question correctly, a simple SQL with ROW_NUMBER would do the job. SQL> select * from test1 order by c1, c2;
C1 C2 SUMC2
-- ---------- ----------
a 1 8
a 1 8
a 1 8
a 1 8
a 2 8
a 2 8
b 2 4
b 2 4
c 1 2
c 1 2
d 1 2
C1 C2 SUMC2
-- ---------- ----------
d 1 2
e 1 2
e 1 2
f 1 2
f 1 2
g 1 2
g 1 2
h 1 2
h 1 2
20 rows selected.
SQL> select c1, c2, sumc2 from (select c1, c2, sumc2, row_number()
over
2 (partition by c1, c2 order by c1, c2) rn from test1) where rn=1;
C1 C2 SUMC2
-- ---------- ----------
a 1 8
a 2 8
b 2 4
c 1 2
d 1 2
e 1 2
f 1 2
g 1 2
h 1 2
9 rows selected.
SQL>
Quote:
> Hi everyonem, how can I do the next: > T is a TABLE
> A B C D E F ....
> 2 1 10 15 10 2
> 2 2 10 15 10 3
> 2 3 12 13 3 4
> 2 4 17 17 3 5
> 2 5 19 5 8 6
> 2 6 19 5 8 1
> 2 7 20 9 12 2
> I need the result: with C,D,E DIFFERENTS !
> A B C D E F ....
> 2 1 10 15 10 2
> 2 3 12 13 3 4
> 2 4 17 17 3 5
> 2 5 19 5 8 6
> 2 7 20 9 12 2
> If I do select C,D,E from T group by C,D,E --> it's OK but i need a select
> with all the rows !!!
> something like: select * fromt T grupo by C,D,E
> Is it possible ? Thank's, OSCAR
|
| Tue, 22 Feb 2005 03:48:30 GMT |
|
| |
|
