CODEWITHSUNDEEP
sqloracle-database
stackoverflow
stackoverflow

Reputation: 69

in database table ,getting records based on one column value and that value is based on other two columns

I have the following table:

 username | timestamp   | distance_location1 | distance_location2
  jack     1532101253000   22.2541(km)           152.652(km)

and query which i am using is 

 select * 
 from   table 
 where  timestamp >= (select min(distance_location1) from table 
   AND  timestamp <= (select min(distance_location2) from table

I want to get the records based on timestamp column.

starting value of timestamp is where minimum distance_location1.

and ending value of timestamp is where minimum distance_location2

above query is not working as it gives 0 records

Upvotes: 0

Views: 44

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You need to compare timestamps, not distances. In Oracle, you can use keep and analytic functions:

select t.* 
from (select t.*,
             min(timestamp) keep (dense_rank first over order by distance_location1) as distance_location1_timestamp,
             min(timestamp) keep (dense_rank first over order by distance_location2) as distance_location2_timestamp
      from table t
     ) t
where timestamp >= distance_location1_timestamp and
      timestamp <= distance_location2_timestamp;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32001

If distance_location1 is data type is timestamp then below query will work where your you put parenthesis in wrong place

select * 
     from   table 
     where  timestamp >=
      ( select min(distance_location1 from table )
       AND  timestamp <= (select min(distance_location2 from table)

But if distance_location1 and distance_location2 is not datatype timestamp 

select * from table
where timestamp>= (select min(timestamp)  from table t where t.distance_location1=(select min(distance_location1) from table)
) and

 timestamp<=( select min(timestamp)  from table t where t.distance_location2=(select min(distance_location2) from table))

Upvotes: 1

Related Questions