Home » SQL & PL/SQL » SQL & PL/SQL » key with different values
key with different values [message #680595] |
Sat, 23 May 2020 16:49 |
desmond30
Messages: 41 Registered: November 2009
|
Member |
|
|
create table mytest(mykey number, mylvl number, myvalue number)
insert into mytest values(10,2,3500);
insert into mytest values(10,2,4500);
insert into mytest values(20,3,1500);
insert into mytest values(20,3,1500);
insert into mytest values(20,2,1500);
insert into mytest values(30,2,3000);
insert into mytest values(30,3,2000);
insert into mytest values(30,2,1000);
insert into mytest values(40,3,500);
insert into mytest values(40,2,700);
insert into mytest values(40,3,400);
insert into mytest values(50,2,3500);
insert into mytest values(50,2,3500);
insert into mytest values(60,2,3000);
insert into mytest values(60,3,3000);
insert into mytest values(60,3,4000);
insert into mytest values(70,2,4500);
insert into mytest values(80,3,5000);
insert into mytest values(80,2,5000);
insert into mytest values(80,2,5000);
insert into mytest values(90,3,2000);
insert into mytest values(90,2,2000);
insert into mytest values(90,3,2000);
insert into mytest values(100,3,4500);
insert into mytest values(100,3,4500);
insert into mytest values(100,2,4500);
insert into mytest values(100,3,6000);
insert into mytest values(100,2,6000);
insert into mytest values(100,2,6000);
insert into mytest values(110,2,6000);
insert into mytest values(110,2,6000);
COMMIT;
i just want to know how to find out mykeys where i have different mylvl values
i tried below, and is not working, example, i want to see 20 and keys like 20 where there are multiple mlvl per each mykey
SELECT MYKEY,MYLVL
FROM MYTEST
GROUP BY MYKEY, MYLVL
HAVING COUNT(*) > 1
[Edit MC: remove more than 100 empty lines between statements]
[Updated on: Sun, 24 May 2020 00:24] by Moderator Report message to a moderator
|
|
|
|
|
Re: key with different values [message #680599 is a reply to message #680595] |
Sun, 24 May 2020 06:22 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
desmond30 wrote on Sat, 23 May 2020 17:49
i just want to know how to find out mykeys where i have different mylvl values
will give you mykeys where mylvl has more than one not null value even if mylvl values are the same. If you want different mylvl values use:
HAVING COUNT(distinct mylvl) > 1
Also, you didn't tell us if you want to count nuuls.
SY.
|
|
|
Goto Forum:
Current Time: Sun Sep 29 01:36:01 CDT 2024
|