remove duplicates 1

[neskin]

Hi there,
I need to count flight_id
flight_id datetime la_max 001 1/1/2001 6:50:15 555 002 1/1/2001 6:50:15 500
002 1/1/2001 6:51:15 550
003 2/1/2001 7:50:15 600
004 2/1/2001 8:50:15 700
004 2/1/2001 9:50:15 800

if flight_id more then once then field la_max needs to be check for maximum then if datetime + or - 2 minutes count only the record with max la_max else count it.

the example above the records count should be 5

001 1/1/2001 6:50:15 555 002 1/1/2001 6:51:15 550
003 1/1/2001 7:50:15 600
004 2/1/2001 8:50:15 700
004 2/1/2001 9:50:15 800


big thanks

nat

 

[Dagon]

You can use analytic functions to do this:

select flight_id, datetime, la_max,
datetime
from
(SELECT flight_id, datetime, la_max,
LAG(datetime, 1, to_date('01-jan-1900', 'DD-MON-YYYY')) OVER (PARTITION BY flight_id ORDER BY datetime) prev_datetime
FROM flights)
where (datetime - prev_datetime) >= (120/86400)

 

[neskin]

hi Dagon,
it is perfect it does work but in my case i have to select record with maximum la_max first and then check the time.
basicly what i need to check
if count flight_id =1 keep it then if count flight_id > 1 then la_max value needs to be checked for this flight_id and then datetime is checking agains
that what i have

flight_id   datetime                       la_max
7179783     06/01/06 8:23:42 PM             790
7179783     06/01/06 8:37:15 PM             788
7188559     13/01/06 9:14:52 PM             695
7188559     13/01/06 9:15:17 PM             721

after using your code i have
flight_id   datetime                       la_max
7179783     06/01/06 8:23:42 PM             790
7179783     06/01/06 8:37:15 PM             788
7188559     13/01/06 9:14:52 PM             695

but i need
flight_id   datetime                       la_max
7179783     06/01/06 8:23:42 PM             790
7179783     06/01/06 8:37:15 PM             788
7188559     13/01/06 9:15:17 PM             721

many thanks
nat

 

[Dagon]

I'm struggling to do it with analytic functions, but you could probably do it with a subquery.

select flight_id, datetime, la_max, datetime
from flights f1
where la_max =
(select max(f2.la_max)
from flights f2
where f1.flight_id = f2.flight_id
and   abs(f2.datetime - f1.datetime) <= (120/86400))

 

[taupirho]

See if this works you:

select flight_id,to_char(datetime,'dd-mon-yy hh:mi:ss'),la_max
from
(SELECT flight_id, datetime, la_max,
LAG(datetime,1,datetime)
OVER (PARTITION BY flight_id ORDER BY datetime desc) prev_datetime,
case when abs(lag(datetime,1,datetime)
over (partition by flight_id order by datetime desc) - datetime) >=(120/86400)
then row_number() over (partition by flight_id order by datetime desc)
when row_number() over (partition by flight_id order by datetime desc) =1
then 1 end rn
FROM flight_table)
where rn is not null

 

[Dagon]

That is only giving the right answer because you have put "desc" on the datetime order by. If you reverse the times on 721 and 695, your query gives:

7179783 06-jan-06 08:37:15 788                                    
7179783 06-jan-06 08:23:42 790                                    
7188559 13-jan-06 09:15:17 695

whereas, according to the requirements, it should be 721.

 

[taupirho]

How about this then:

select flight_id,to_char(datetime,'dd-mon-yy hh:mi:ss'),la_max
from
(SELECT flight_id, datetime, la_max,
LAG(datetime,1,datetime)
OVER (PARTITION BY flight_id ORDER BY flight_id,datetime desc) prev_datetime,
case when lag(datetime,1,datetime)
over (partition by flight_id order by flight_id,datetime desc) - datetime
>=(120/86400)
then row_number()
over (partition by flight_id order by flight_id,datetime desc)
when row_number()
over (partition by flight_id order by flight_id,datetime desc) =1
then 1 end rn
FROM tom2)
where rn is not null

 

[Dagon]

It doesn't look much different to me. I still don't see anything which is getting the maximum la_max within the subset of flight_ids which went out at the same time. Also, what is TOM2 ? Is that different to the original flight table ?

 

[neskin]

hi guys,
this is what i ended up with .May be u just correct me with more efficient way

select flight_id,la_max
from
(select a.flight_id,a.la_max_time,a.prev_datetime,a.la_max,b.max_lm,
       (case when a.la_max = b.max_lm then 1 else 0 end)max_la_max
from
(SELECT flight_id,datetime, la_max, 
LAG(datetime, 1,datetime) OVER (PARTITION BY flight_id ORDER BY datetime) prev_datetime
  
FROM flights

and datetime between to_date('01/01/2006','dd/mm/yyyy') and to_date('31/03/2006','dd/mm/yyyy')and
    flight_id <>0 )a,

(select flight_id,max(la_max) max_LM
from flights

and datetime between to_date('01/01/2006','dd/mm/yyyy') and to_date('31/03/2006','dd/mm/yyyy')and
    flight_id <>0 
group by flight_id)b
where a.flight_id = b.flight_id )c
where max_la_max = 1 or abs(la_max_time - prev_datetime) >= (120/86400)

thank u
Nat

 

[Dagon]

I think you must have done a bit of simplification on that without keeping it syntactically correct. For example, you are missing the "where" keyword in the inline views. Also, what is "la_max_time" ? Is it an additional column to datetime or is it meant to be derived somewhere in the query ?

 

[Dagon]

Assuming what you actually intend is:

select flight_id, max_lm
from
(select a.flight_id,a.prev_datetime, a.datetime, a.la_max, b.max_lm,
       (case when a.la_max = b.max_lm then 1 else 0 end) max_la_max
from
(SELECT flight_id, datetime, la_max,
LAG(datetime, 1,datetime) OVER (PARTITION BY flight_id ORDER BY datetime) prev_datetime
FROM flights) a,
(select flight_id,max(la_max) max_LM
from flights
group by flight_id) b
where a.flight_id = b.flight_id ) c
where max_la_max = 1 or abs(datetime - prev_datetime) >= (120/86400)

(I have got rid of some of the where conditions which were not required for my simplified model of the table).

I still think there may be an inherent problem. Suppose you have a situation like this:

flight_id   datetime                       la_max
1           13:01                          10
1           13:02                          20
1           17:01                          15
1           17:02                          30

My understanding of your requirements is that you would need to get:

flight_id   datetime                       la_max
1           13:02                          20
1           17:02                          30

However, I believe your SQL would return:

flight_id   datetime                       la_max
1           13:02                          30
1           17:02                          30

However, I may have misunderstood your requirements or this may not be a situation which arises.

If you simply want the maximum(la_max) regardless of which timeslot it appears in, you could get this much more simply using something like:

select flight_id, datetime, la_max,
datetime, max_la_max
from
(SELECT flight_id, datetime, la_max,
LAG(datetime, 1, to_date('01-jan-1900', 'DD-MON-YYYY')) OVER (PARTITION BY flight_id ORDER BY datetime) prev_datetime,
MAX(LA_MAX) OVER (PARTITION BY flight_id) max_la_max
FROM flights)
where (datetime - prev_datetime) >= (120/86400)

 

[neskin]

hi dagon,
thank u for the helpfull queries i will use them but i still can not get i want
la_max_time is the same field as datetime i just simplifyed for the query purpose.
u havent misunderstood me and u r right i hit the problem where like u said

flight_id   datetime                       la_max
1           13:01                          10
1           13:02                          20
1           17:01                          15
1           17:02                          30

the record with la_max maximum needs to be shonw no matter what then datetime needs to compare agains Max(la_max) datetime and then if it is less or greater then 2 min do not display . in our example will show

flight_id   datetime                       la_max
1           17:01                          15
1           17:02                          30

thank u
Nat

 

[Dagon]

Did you try the subquery version I suggested earlier ?

 

[SantaMufasa]

So, Neskin, is it correct that you want to see, for each flight_id, its record that contains "max(la_max)", plus any rows that are within 2 minutes of the max's datetime?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[neskin]

hi SantaMufasa
yes u r correct
Nat

 

[neskin]

Hi Dagon,
i ahve tryed your subquery it is work for me !!!
thank u