Home » SQL & PL/SQL » SQL & PL/SQL » key with different values
key with different values [message #680595] Sat, 23 May 2020 16:49 Go to next message
desmond30
Messages: 33
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 #680596 is a reply to message #680595] Sun, 24 May 2020 00:29 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your specifications are not clear.
Provide the result you want for the data you gave, formatted and columns aligned.

And Do NOT useless empty lines, there were twice more blank lines and not blank ones in your post.

Re: key with different values [message #680597 is a reply to message #680596] Sun, 24 May 2020 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 67367
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also always post your Oracle version (query v$version).
And terminate your statement, your CREATE TABLE does not end with a ";" or "/".

Re: key with different values [message #680599 is a reply to message #680595] Sun, 24 May 2020 06:22 Go to previous message
Solomon Yakobson
Messages: 2985
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


HAVING COUNT(*) > 1
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.
Previous Topic: Using associated array as SP parameter
Next Topic: Missing number in series of numbers
Goto Forum:
  


Current Time: Wed Sep 23 00:42:50 CDT 2020