group by without max

[gdg1976]

hi all,

can i select all rows from a table where there are more rows with the same column A and i don't want to extract column A with the hight column b
when there are more equal rows for columns A.

example

[pre]column a column b
a 1
b 2
c 3
c 4
d 5
d 6
d 7[/pre]

[pre]result table will be
column a column b
c 3
d 5
d 6[/pre]

thanks in advance

 

[mikrom]

Hi gdg1976,

When I have more complex queries in SQL, I use CTEs (Common Table Expressions) for the sake of clarity. That means, I create several temporary tables one after the other and thus approach the solution step by step.

On the beginning I have TAB1

create or replace table TAB1 (
  A character(1),
  B decimal(1, 0)
)
;
insert into TAB1 values
    ('a', 1),
    ('b', 2), 
    ('c', 3),
    ('c', 4),
    ('d', 5), 
    ('d', 6),
    ('d', 7) 
;

select * from TAB1
;

which contains these data

A       B
---------
a	1
b	2
c	3
c	4
d	5
d	6
d	7

Then it starts with CTEs: I define successively as needed temporary tables until I'm able to get desired result, in this case there are tables TAB2 to TAB5:

with 
TAB2(A) as (
  select A from TAB1
  group by A
  having count(*) > 1
),
TAB3(A, B) as (
  select t1.A, t1.B 
  from TAB1 as t1 join TAB2 as t2 on t1.A = t2.A
),
TAB4(A, M) as (
  select A, max(B) as M
  from TAB3
  group by A     
),
TAB5(A, B, M) as (
  select t3.A, t3.B, t4.M 
  from TAB3 as t3 join TAB4 as t4 on t3.A = t4.A
)
select A, B from TAB5
where B < M
;

The result is:

A       B
---------
c	3
d	5
d	6

 

[gdg1976]

Hi MIKROM,

thanks, your replay is very useful.

I would like to ask you what do you think about this solution ?

[pre]select * from tab1
where columnA in
(select columnA from tab1
GROUP BY columnA
HAVING COUNT(*) > 1)
and columnB not in
(select max(columnB) from tab1
GROUP BY columnA
HAVING COUNT(*) > 1)[/pre]

many thanks
bye

 

[mikrom]

Hi gdg1976,
Your solution is simpler and better than mine