order and then compare values from same column

Expand Post »

Hello everyone,

I am fairly new to oracle and SQL and I am having a little trouble figuring out how to accomplish a task.

I have three columns from a table, we will call them 'a', 'b', and 'id'. I first want to order by 'id' and then query the ordered data with the following condition.

I want to add the value at column 'a' to the value at column 'b' and then check and see if that sum is equal to the next value in column 'b'.

If that is not possible, I may also be able to add up column 'a' and column 'b' (take the sum of each column, grouped by 'id') and then subtract the last value in column 'a' grouped to each id from column 'a' and then compare the value of column a (sum(a) - last_value(a), with respect to the id from the 'id' column) with the sum of column 'b' (sum(b))

Any help would be appreciated!

Thanks,

Nick

Similar Threads

compare values in a tuple/list in Python
Cmp?? in Assembly
Compare two tables in MS SQL
Can any1 help me...this is urgent in Java
I would kill for this, just a little bit in C

stupidenator

Reputation Points: 18

Solved Threads: 4

Junior Poster

Offline

192 posts
since Mar 2005

Permalink

Jan 29th, 2007

Re: order and then compare values from same column

This doesn't appear to be a task that can be performed with a single SQL if that was what you were looking for. Even if it is possible (because I'm not very good at SQL myself), the query itself is so dependent on the results that having a stored procedure and using a cursor would be a good choice.

aniseed

Reputation Points: 48

Solved Threads: 7

Posting Whiz

Offline

353 posts
since Apr 2006

Permalink

Jan 31st, 2007

Re: order and then compare values from same column

Thank you for your help. Below is what I eventually came up with as a solution.

Oracle Syntax (Toggle Plain Text)
SELECT UNIQUE id
FROM table1 a
LEFT JOIN table1 b
ON a.value1 + a.prev_value <> b.prev_value
WHERE TO_NUMBER(b.amd_no) = (TO_NUMBER(a.amd_no) + 1)
AND a.id = b.id;
SELECT UNIQUE id
FROM table1 a
LEFT JOIN table1 b
ON a.value1 + a.prev_value <> b.prev_value
WHERE to_number(b.amd_no) = (to_number(a.amd_no) + 1)
AND a.id = b.id;

Thanks,

Nick

stupidenator

Reputation Points: 18

Solved Threads: 4

Junior Poster

Offline

192 posts
since Mar 2005

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Oracle Forum Timeline: using jsp for a calendar connected to oracle

Next Thread in Oracle Forum Timeline: Cartesian Product Problem

DaniWeb Message

Cancel Changes

User Name
Password