Home » SQL & PL/SQL » SQL & PL/SQL » Analytic Query
Analytic Query [message #338730] Tue, 05 August 2008 18:42 Go to next message
honkeyhindu
Messages: 2
Registered: August 2008
Junior Member
I have a table with three columns

Item Number : Based on Item number : Submitted Date

I want to know how many item_numbers there are that have a Based on item number that changes.

Item Number : Based on Item number : Submitted Date

1000 : 2345 :
1000 : 2345 :
1000 : 6756 :
2000 : 6789
2000 : 4567
2000 : 6789

the based on item number for each item number is supposed to be consistent i.e if the item number is say 1000 and the based on item number is 6 it should stay that way for that particulr item number but for some reason they are not and i need to find out how many times it has happened.

[Updated on: Tue, 05 August 2008 18:51]

Report message to a moderator

Re: Analytic Query [message #338731 is a reply to message #338730] Tue, 05 August 2008 18:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


>the based on item number for each item number is supposed to be consistent
HUH? What is supposed to be consistent with what?

Forgive me but I don't understand what problem you need assistance in solving.

[Updated on: Tue, 05 August 2008 18:47] by Moderator

Report message to a moderator

Re: Analytic Query [message #338732 is a reply to message #338731] Tue, 05 August 2008 18:57 Go to previous messageGo to next message
honkeyhindu
Messages: 2
Registered: August 2008
Junior Member
What is supposed to be consistent with what?

thanks for the quick reply

The based on item number is picked by a user in a application

lets say that the item_number is 1000 and the user picks a based_on_item number of 60. Then for the life of that item_number the based_on_item_number should always be 60

this is how the table should look

Item_number : Based_on_item_number

1000: 60
1000: 60
1000: 60
2000: 45
2000: 45

but its not, in some cases there are item_number where the based_on_item_number changes

hopefully that clears up some confusion
Re: Analytic Query [message #338738 is a reply to message #338730] Tue, 05 August 2008 20:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Analytic Query [message #338752 is a reply to message #338730] Tue, 05 August 2008 21:00 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You could use the lag or lead analytic function to compare the prior based_on_item_number to the next based_on_item_number within each group of item_number ordered by submitted_date.

Re: Analytic Query [message #338760 is a reply to message #338730] Tue, 05 August 2008 21:13 Go to previous message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
SELECT DISTINCT item_number, based_on_item_number
        FROM some_table

will give you all the item_number/based_on_item_number combinations. If I understand your comments, each item_number should appear just once in this result set.

So, a little more grouping should find those item_numbers with multiple based_on_item_numbers:

SELECT item_number, count(*) from (
           SELECT DISTINCT item_number, based_on_item_number
              FROM some_table
                                         )
        GROUP BY item_number
        HAVING count(*) > 1

should be what you want.

Unless you need to track things over time, and Barbara's comment becomes relevant. But, either I am way off the track, or there is more to this than your simple description.

Previous Topic: Automate Custom SQL Menu
Next Topic: Bulk insert is very slow
Goto Forum:
  


Current Time: Sat Dec 10 03:02:29 CST 2016

Total time taken to generate the page: 0.33361 seconds