Home » SQL & PL/SQL » SQL & PL/SQL » complicated distinct query
complicated distinct query [message #194572] Fri, 22 September 2006 13:56 Go to next message
cruwyser
Messages: 6
Registered: September 2006
Junior Member
Hi All,

Newbie to this forum here, i have a bit of a technical query which i am trying to do and would love any help that anyone could offer.

I am currently using Oracle 8i. I have a directdebit table where I am trying to show the Direct Debit amount changes for every account.

Everytime the Direct Debit changes, the database creates a new row in the table with the new data, regardless if the amount has changed or now. (see below)

I just want to show account number, the directdebit amount and the date it was entered. The query should only return the results with a star shown below.

ACCOUNTNO NAME AMOUNT DATE REQUIRED
123456 Paul & John 22.40 01/09/06 *
123456 Paul & John 30.00 02/09/06 *
123456 Paul & Tom 30.00 03/09/06
123456 Paul & Clare 30.00 04/09/06
123456 Paul & Clare 50.00 05/09/06 *
123456 Paul & Dave 50.00 06/09/06
123456 Paul & Dave 30.00 07/09/06 *
123456 Paul & Janet 30.00 08/09/06


Thanks in advance for your help! Razz
Re: complicated distinct query [message #194579 is a reply to message #194572] Fri, 22 September 2006 15:49 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
could you provide a bit more information on the display criteria? Is "Required" a column or is it part of "DateRequired"? If it is a column, then the query can be something like:

select ACCOUNTNO, AMOUNT, DATE from DIRECTDEBIT where REQUIRED='*'


Gojko Adzic
http://www.gojko.com
Re: complicated distinct query [message #194580 is a reply to message #194579] Fri, 22 September 2006 15:52 Go to previous messageGo to next message
cruwyser
Messages: 6
Registered: September 2006
Junior Member
Hi Gojko,

Sorry i didnt explain too well. The column named "Required" doesnt exist, but just shows the rows the query should return.

The query should hopefully end up looking like this based on the data above:


ACCOUNTNO NAME AMOUNT DATE
123456 Paul & John 22.40 01/09/06
123456 Paul & John 30.00 02/09/06
123456 Paul & Clare 50.00 05/09/06
123456 Paul & Dave 30.00 07/09/06

[Updated on: Fri, 22 September 2006 15:53]

Report message to a moderator

Re: complicated distinct query [message #194581 is a reply to message #194580] Fri, 22 September 2006 15:58 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
it's still not clear what the criteria for 'required' is... it's not ordered by date, nor unique by account. What are you trying to achieve with that query?

Gojko Adzic
http://www.gojko.com
Re: complicated distinct query [message #194582 is a reply to message #194581] Fri, 22 September 2006 16:13 Go to previous messageGo to next message
cruwyser
Messages: 6
Registered: September 2006
Junior Member
We want to be able to see the history of Direct Debit amounts, but when someone changes the name on the DD it adds another row to the table.

If i do "select distinct accountno, amount" the system will hide any duplicates but if the amount happened to be the same previously, this result is omitted.

Basically, I want to be able to see the date the amount changed (based on the date field) and the amount that it was set to (based on amount field).

I do not want the rows where the amount is the same, but the name has changed - Just when the amount has been changed
Re: complicated distinct query [message #194584 is a reply to message #194582] Fri, 22 September 2006 16:48 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If that's so, perhaps something like this (or its variations) might help:
SELECT   t.amount, t.dat_req
    FROM TEST t
   WHERE t.dat_req = (SELECT MIN (dat_req) FROM TEST)
UNION
SELECT   t2.amount, t2.dat_req
    FROM TEST t1, TEST t2
   WHERE t1.dat_req = t2.dat_req - 1 
     AND t1.amount <> t2.amount
ORDER BY 1;
First part of a query is here just to select the first record - it, actually, didn't change (it was first, right?). Query after the UNION operator does the job. Date column condition is crucial here, as it determines which values are to be compared. However, if dates don't change by 1 (day after thay), this part should be adjusted.
Re: complicated distinct query [message #194586 is a reply to message #194584] Fri, 22 September 2006 17:01 Go to previous messageGo to next message
cruwyser
Messages: 6
Registered: September 2006
Junior Member
Thanks for the reply. I have modifed the code to represent the column/tables names in the database. it only seems to produce the top query as im not too sure about the rest?

Is it possible to work this out for everyrow and also if the date field is not always incremented by the same number (ie could be 1, then 11, then 56, etc)
Re: complicated distinct query [message #194587 is a reply to message #194586] Fri, 22 September 2006 17:11 Go to previous messageGo to next message
gojko
Messages: 18
Registered: September 2006
Location: London
Junior Member
You can use lag and lead (depending on the way you sort the rows) to compare the amount of the current row with the corresponding amount of the next (or previous) row for the same account. See http://www.orafaq.com/node/55 for an example.

Gojko Adzic
http://www.gojko.com
Re: complicated distinct query [message #194590 is a reply to message #194587] Fri, 22 September 2006 17:46 Go to previous message
cruwyser
Messages: 6
Registered: September 2006
Junior Member
Thank you Gojko! Genious!

The suggestion you made has solved the problem. There are also a lot of useful commands on the link you sent me Cool

Thanks once again
Paul
Previous Topic: Joining 2 rows into 1
Next Topic: Bad Left Outer Join Optimisation
Goto Forum:
  


Current Time: Fri Dec 02 18:54:12 CST 2016

Total time taken to generate the page: 0.18559 seconds