CODEWITHSUNDEEP
sqloracle-database
Martin
Martin

Reputation: 1090

Finding entry with maximum date range between two columns in SQL

Note: this is in Oracle not MySQL, limit/top won't work.

I want to return the name of the person that has stayed the longest in a hotel. The longest stay can be found by subtracting the date in the checkout column with the checkin column. So far I have:

select fans.name 
from fans 
where fans.checkout-fans.checkin is not null
order by fans.checkout-fans.checkin desc;

but this only orders the length of stay of each person from highest to lowest. I want it to only return the name (or names, if they are tied) of people who have stayed the longest. Also, As more than one person could have stayed for the highest length of time, simply adding limit 1 to the end won't do.

Edit (for gbn), when adding a join to get checkin/checkout from other table it wont work (no records returned)

edit 2 solved now, the below join should of been players.team = teams.name

select
   x.name
from
    (
    select
        players.name,
        dense_rank() over (order by teams.checkout-teams.checkin desc) as rnk
    from
        players
    join teams
    on players.name = teams.name
    where
        teams.checkout-teams.checkin is not null
    ) x
where
   x.rnk = 1

Upvotes: 0

Views: 383

Answers (6)

Pritesh Tayade
Pritesh Tayade

Reputation: 630

If both the columns are date fields you can use this query:

select fans.name from fans where fans.checkout-fans.checkin in (select max(fans.checkout-fans.checkin) from fans );

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Another way that should work in all dbms (or almost all, at least those that support subqueries):

select fans.name 
from fans 
where fans.checkout-fans.checkin = 
      ( select max(f.checkout-f.checkin) 
        from fans f
      ) ;

Upvotes: 0

gbn
gbn

Reputation: 432200

Should be this using DENSE_RANK to get ties

select
   x.name
from
    (
    select
        fans.name,
        dense_rank() over (order by fans.checkout-fans.checkin desc) as rnk
    from
        fans 
    where
        fans.checkout-fans.checkin is not null
    ) x
where
   x.rnk = 1;

SQL Server has TOP..WITH TIES for this, but this is a generic solution for any RDBMS that has DENSE_RANK.

Upvotes: 3

Mehmet Balioglu
Mehmet Balioglu

Reputation: 2302

For Oracle:

select * from 
(
select fans.name
from fans 
where fans.checkout-fans.checkin is not null
order by fans.checkout-fans.checkin desc)
where rownum=1

Upvotes: 0

Denis Malinovsky
Denis Malinovsky

Reputation: 6240

Try this:

select name, (checkout-checkin) AS stay
from fans 
where stay is not null -- remove fans that never stayed at a hotel
order by stay desc;

Upvotes: 0

Semih Yagcioglu
Semih Yagcioglu

Reputation: 4101

Longest is a fuzzy word, you should first define what is long for you. Using limit may not be a solution for this case. So you can define your treshold and try to filter your results where fans.checkout-fans.checkin > 10 for instance.

Upvotes: 0

Related Questions