Home » SQL & PL/SQL » SQL & PL/SQL » Query to select a column value fro which other column is highest.
Query to select a column value fro which other column is highest. [message #408067] Sat, 13 June 2009 10:03 Go to next message
gigeman
Messages: 3
Registered: June 2009
Junior Member
Hi all.

Could anyone please help me here.
I 've a table with name say "audit" with columns
uid number, old varchar, new varchar ,num number (unique).
a sample data of table is below:

uid |old_r | new_r | num
1 |null | R1 | 1
1 |R1 | null | 2
1 |null| | R2 | 3
1 |R2 | null | 4
1 |null| | R1 | 5

Now for a given uid value I want to select all distinct values in column new for which the *last* entry in column new occurs after the *last* occurence of that value in column old. "Last" here means the value in column num is highest.

I am trying some thing like:

select a1.new_r, max(a1.num) over (partition by a1.new_r) max_num_new,
max(a2.num) over (partition by a2.old_r) max_num_old
from audit a1, audit a2 where
a1.uid=1 and
a2.uid=1 and
a1.new_r=a2.old_r and
max_num_old<max_num_new;

Now here "max_num_old<max_num_new" throws error.
Without that it works but I want the comparison.
Could somebody let me know how can this check be included.
Also how could I get distinct rows, because this query (without "max_num_old<max_num_new" )returns some duplicate rows.

Your help,suggestions are highly appreciated.

Thanks









Re: Query to select a column value fro which other column is highest. [message #408069 is a reply to message #408067] Sat, 13 June 2009 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>Now here "max_num_old<max_num_new" throws error.
ERROR? What Error? I do not see any error.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Query to select a column value fro which other column is highest. [message #408074 is a reply to message #408069] Sat, 13 June 2009 10:42 Go to previous messageGo to next message
gigeman
Messages: 3
Registered: June 2009
Junior Member
Here it is

DDL:
CREATE TABLE "audit"
( "UID" NUMBER,
"OLD_R" VARCHAR2(50 BYTE),
"NEW_R" VARCHAR2(50 BYTE),
"NUM" NUMBER NOT NULL ENABLE,
CONSTRAINT "TEST_USER_ROLE_AUDIT_PK" PRIMARY KEY ("NUM")
)

DMLs:


INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES,('1', 'null', 'R1', '1');
INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES, ('2', 'null', 'R1', '2');
INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES, ('1', 'R1', 'null', '3');
INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES, ('1', 'null', 'R2', '4');
INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES, ('1', 'R2', 'null', '5');
INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES, ('1', 'null', 'R1', '6');
INSERT INTO "audit" (UID, OLD_R, NEW_R, NUM) VALUES, ('2', 'R1', 'null', '7');

apologies to miss this.
Re: Query to select a column value fro which other column is highest. [message #408075 is a reply to message #408074] Sat, 13 June 2009 10:50 Go to previous messageGo to next message
gigeman
Messages: 3
Registered: June 2009
Junior Member
Iam using :
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit
on a unix machine.

The erro obtained is :
SQL Error: ORA-00904: "MAX_NUM_NEW": invalid identifier

[Updated on: Sat, 13 June 2009 10:56]

Report message to a moderator

Re: Query to select a column value fro which other column is highest. [message #408078 is a reply to message #408067] Sat, 13 June 2009 11:11 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>SQL Error: ORA-00904: "MAX_NUM_NEW": invalid identifier
error occurs because "MAX_NUM_NEW" has never been defined.
Perhaps you want MAX(NUM) instead.
Re: Query to select a column value fro which other column is highest. [message #408085 is a reply to message #408075] Sat, 13 June 2009 13:25 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't use in where claure alias defined in select clause.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Previous Topic: How to create Charts ino Excel using stored procedure
Next Topic: PLS-00222 error
Goto Forum:
  


Current Time: Thu Dec 08 04:31:04 CST 2016

Total time taken to generate the page: 0.08248 seconds