Suche nach kleinster Datumsdifferenz

[X-Dream]

Hallo zusammen

Ich hab grad eine Aufgabe, die meine SQL-Kenntnisse leicht übersteigt. Evtl. kann mir jemand von Euch damit weiterhelfen.

Ich möchte aus der Tabelle 'Order_In' denjenigen Datensatz mit der kleinsten Differenz zum aktuellen Systemdatum:

Order_No Start_Time
1234567 03.09.15 08:25:35
2356455 04.09.15 14:46:38
2348792 05.09.15 04:58:12

Ich hab mal soweit folgende query erstellt:

SelectTrunc(SysDate) - Start_Time As Difference
From Order_In
Where Difference = (Select Min(Difference) From Order_In);

Doch leider scheint der Audruck 'Difference' in der Where-Klausel nicht akzeptiert zu werden.
Hat jemand eine Idee, was ich falsch mache?

Gruss
X-Dream

 

[Distrilec]

Select *
from (Select Max(start_time) Keep (dense_rank first Order By (trunc(sysdate) - trunc(start_time)) Asc)
      From order_in)
Where rownum = 1

Das sollte es wohl tun... (Nicht getestet)

Edit:
Was du falsch machst -> Deine Where bedingung enthält ein Sub-Select (ein neues Select auf die Tabelle "order_in") und offensichtlich gibt es in dieser Tabelle keine Spalte "Difference"

 

[akretschmer]

ich weiß nicht, ob Oracle das kann, aber PostgreSQL kann via KNN-Indexe solch eine Suche sogar Indexbasiert.

test=# create table d as select ('2015-08-20'::date + s * '1day'::interval)::date  as datum from generate_series(0,20) s;
SELECT 21
test=*# select * from d;
  datum
------------
 2015-08-20
 2015-08-21
 2015-08-22
 2015-08-23
 2015-08-24
 2015-08-25
 2015-08-26
 2015-08-27
 2015-08-28
 2015-08-29
 2015-08-30
 2015-08-31
 2015-09-01
 2015-09-02
 2015-09-03
 2015-09-04
 2015-09-05
 2015-09-06
 2015-09-07
 2015-09-08
 2015-09-09
(21 rows)

test=*# create index idx_knn on d using gist(datum);
CREATE INDEX
test=*#

test=*# explain select datum, current_date <-> datum from d order by current_date <-> datum limit 1;
  QUERY PLAN
-----------------------------------------------------------------------------
 Limit  (cost=0.14..0.55 rows=1 width=4)
  ->  Index Only Scan using idx_knn on d  (cost=0.14..8.67 rows=21 width=4)
  Order By: (datum <-> ('now'::cstring)::date)
(3 rows)

test=*# select datum, current_date <-> datum from d order by current_date <-> datum limit 1;
  datum  | ?column?
------------+----------
 2015-09-04 |  0
(1 row)

Falls Ora das nicht kann dann 2 Abfragen

test=*# select datum, datum - current_date from d where datum >= current_date order by 2 ;
  datum  | ?column?
------------+----------
 2015-09-04 |  0
 2015-09-05 |  1
 2015-09-06 |  2
 2015-09-07 |  3
 2015-09-08 |  4
 2015-09-09 |  5
(6 rows)

test=*# select datum, current_date - datum from d where datum <= current_date order by 2 ;
  datum  | ?column?
------------+----------
 2015-09-04 |  0
 2015-09-03 |  1
 2015-09-02 |  2
 2015-09-01 |  3
 2015-08-31 |  4
 2015-08-30 |  5
 2015-08-29 |  6
 2015-08-28 |  7
 2015-08-27 |  8
 2015-08-26 |  9
 2015-08-25 |  10
 2015-08-24 |  11
 2015-08-23 |  12
 2015-08-22 |  13
 2015-08-21 |  14
 2015-08-20 |  15
(16 rows)

Das via UNION ALL zusammenfassen, nach der zweiten Spalte sortieren mit LIMIT 1.

 

[X-Dream]

Select *
from (Select Max(start_time) Keep (dense_rank first Order By (trunc(sysdate) - trunc(start_time)) Asc)
      From order_in)
Where rownum = 1

Hallo Distrilec
Da scheint noch irgendwo ein Syntaxfehler zu sein. Mal schauen ob ich's raus finde...

Gruss
X-Dream

 

[X-Dream]

Ich konnte den Code etwas vereinfachen und damit erhalte ich nun den record, der dem Systemdatum am nächsten ist:

select *
from (select min(trunc(SysDate) - TERM_START) from ORDER_IN)

Aber wie bekomm ich jetzt die restlichen Felder der Tabelle ORDER_IN noch mit in die ABfrage?

Gruss
X-Dream

 

[akretschmer]

siehe meine Lösung ...

 

[X-Dream]

Hallo akretschmer und Distrilec

Ich musste den Code für ora etwas anpassen aber so funktioniert es...
Danke Euch beiden.

Gruss
X-Dream

 

[akretschmer]

Der Abstands-Operator

order by current_date <-> datum

also das <->, ist schon cool unter PG, oder? Und Indexbasiert! ;-)

 

[Distrilec]

Hallo Distrilec
Da scheint noch irgendwo ein Syntaxfehler zu sein. Mal schauen ob ich's raus finde...

Gruss
X-Dream

Was für ne Version hast du denn am Laufen, wenn das einen Syntaxfehler wirft?

Ich konnte den Code etwas vereinfachen und damit erhalte ich nun den record, der dem Systemdatum am nächsten ist:

select *
from (select min(trunc(SysDate) - TERM_START) from ORDER_IN)

Aber wie bekomm ich jetzt die restlichen Felder der Tabelle ORDER_IN noch mit in die ABfrage?

Gruss
X-Dream

Das wird nicht funktionieren...
Sieh dir die Lösung von dem hier an:

With dat As
(
Select to_date('31.01.2000', 'DD.MM.YYYY') As val From dual Union All
Select trunc(sysdate) From dual Union All
Select to_date('31.01.2200', 'DD.MM.YYYY'))

Select Min(trunc(sysdate) - val) From dat

Edit:
Ja, KNN-Indizes gehen in Oracle auch... Aber das ist Oracle Spatial... da bin ich leider raus