CODEWITHSUNDEEP
sql
abatishchev
abatishchev

Reputation: 100258

It is possible to get the top 1 (latest) record without using ORDER BY clause?

I'm using some academic DBMS and don't know does it implement limit/top feature. Is it possible to achieve the same using any other technique?

Say I have next tables:

Movies

MovieId
Title
Year

Actors

ActorId
Name

ActorInMovie

ActorId
MovieId

And what get the latest by year movie of given by name actor, e.g.:

select m.Title, m.Year
from ActorInMovie in
join Movies m on m.MovieId = in.MovieId
join Actors a on a.ActorId = in.ActorId
where
    a.Name = 'Bruce Willis'
and m.Year = ...

Upvotes: 1

Views: 231

Answers (5)

Bohemian
Bohemian

Reputation: 424993

If the "latest" is defined as the most recent timestamp of your records:

select *
from your_tables
where your_conditions
and timestamp = (
    select max(timestamp) 
    from your_tables
    where your_conditions
)

This could get more than one record if there happen to be two identical timestamps for the same where condition, but typically it would be extremely unlikely.

Upvotes: 2

Ariel
Ariel

Reputation: 26753

SELECT m.Title, m.Year FROM ActorInMovie in
join Movies m on m.MovieId = in.MovieId
join Actors a on a.ActorId = in.ActorId
where
  a.Name = 'Bruce Willis'
  AND m.Year = (
    SELECT MAX(year) FROM ActorInMovie in
    join Movies m on m.MovieId = in.MovieId
    join Actors a on a.ActorId = in.ActorId
    where
      a.Name = 'Bruce Willis'
  )

You have not said what to do if there is more than one title in a given year. And I really hope your database supports subqueries.

Upvotes: 2

Nicholas Carey
Nicholas Carey

Reputation: 74227

Not knowing anything about the database you're using... standard sql allows one to say

-- set result set size to 1 row
set rowcount 1

-- execute select statement of choice
select *
from foo

-- restore unlimited result set size
set rowcount 0

That being said, if you don't apply an ordering with ORDER BY, which 1 row gets returned is not guaranteed, though, in most implementation, the same row will usually be returned each time.

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135799

A couple of thoughts:

  1. Use a window function (e.g., ROW_NUMBER()) in a subquery and select where the row number is 1. But if your DBMS isn't sophisticated enough to have TOP/LIMIT, it seems unlikely that it would have window functions.

  2. Insert the results into a table with an auto-incrementing identity type column. Then select where that identity column is 1.

Upvotes: 5

Larry Lustig
Larry Lustig

Reputation: 50970

It's quite possible, as long as you don't mind getting all the movies the actor made in the most recent year. The trick is to derive, in an internal SELECT, the most recent year for the actor.

 select m.Title, m.Year
 from ActorInMovie in
 join Movies m on m.MovieId = in.MovieId
 join Actors a on a.ActorId = in.ActorId
 where
     a.Name = 'Bruce Willis'
 and m.Year = (SELECT MAX(m2.Year) 
    from ActorInMovie in2
    join Movies m2 on m2.MovieId = in2.MovieId
    join Actors a2 on a2.ActorId = in2.ActorId
    where a.Name = 'Bruce Willis')

Upvotes: 4

Related Questions