Oracle SQL Get the First and Last Records from an Ordered Dataset

Oracle SQL get the first and last records from an ordered dataset

You can use window functions:

select t.*
from (select t.*, row_number() over (order by date_created) as seqnum,
             count(*) over () as cnt
      from t
     ) t
where seqnum = 1 or seqnum = cnt;

In Oracle 12, you can also do:

select t.*
from t
order by date_created
fetch first 1 rows only
union all
select t.*
from t
order by date_created desc
fetch first 1 rows only;

How to select top 1 and ordered by date in Oracle SQL?

... where rownum = 1 order by trans_date desc

This selects one record arbitrarily chosen (where rownum = 1) and then sorts this one record (order by trans_date desc).

As shown by Ivan you can use a subquery where you order the records and then keep the first record with where rownum = 1in the outer query. This, however, is extremely Oracle-specific and violates the SQL standard where a subquery result is considered unordered (i.e. the order by clause can be ignored by the DBMS).

So better go with the standard solution. As of Oracle 12c:

select * 
from table_name 
order by trans_date desc
fetch first 1 row only;

In older versions:

select *
from
(
  select t.*, row_number() over (order by trans_date desc) as rn
  from table_name t
)
where rn = 1;

How to get First and Last record from a sql query?

[Caveat: Might not be the most efficient way to do it]:

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC
LIMIT 1)

UNION ALL

(SELECT <some columns>
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date ASC    
LIMIT 1)

Oracle SQL Return First & Last Value From Different Columns By Partition

Use first/last option to find statuses. The rest is classic aggregation:

select employee, min(start_), sum(duration),
       max(init_status) keep (dense_rank first order by start_),
       max(fin_status)  keep (dense_rank last  order by start_)
  from test_data t
  group by employee, item_id
  order by employee, item_id;

start is a reserved word, so I used start_ for my test.

Find the first AND last date that an ID occured in dataset SQL

Simply do a GROUP BY. Use MIN() and MAX() to get first and last date.

select OrderID,
       MIN(SnapShotDate) as FirstDateOccuring,
       MAX(SnapShotDate) as LastDateOccuring 
from tablename
group by OrderID

How to select first and last record from ordered query without UNION, INTERSECT etc

Similar to @vkp's approach, but without the join back to the base table:

select function, avg_salary
from (
  select function, avg(salary) as avg_salary,
    rank() over (order by avg(salary)) as rnk_asc,
    rank() over (order by avg(salary) desc) as rnk_desc
  from tablename
  group by function
)
where rnk_asc = 1 or rnk_desc = 1;

F AVG_SALARY
- ----------
D        800
A        175

The two rank() calls put each function/average in order:

select function, avg(salary) as avg_salary,
  rank() over (order by avg(salary)) as rnk_asc,
  rank() over (order by avg(salary) desc) as rnk_desc
from tablename
group by function;

F AVG_SALARY    RNK_ASC   RNK_DESC
- ---------- ---------- ----------
D        800          6          1
B    4.7E+02          5          2
F        325          4          3
C        250          3          4
E        225          2          5
A        175          1          6

That forms the inline view; the outer query then just selects the rows ranked 1 in either of the generated columns, which is D and A here.

If you had two functions with the same average salary then they would get the same rank, and both ranked as 1 then you'd see both; so you can get more than two results. That may be what you want. If not you can avoid it by defining how to break ties, either with rank() or dense_rank().

How can I get just the first row in a result set AFTER ordering?

This question is similar to How do I limit the number of rows returned by an Oracle query after ordering?.

It talks about how to implement a MySQL limit on an oracle database which judging by your tags and post is what you are using.

The relevant section is:

select *
from  
  ( select * 
  from emp 
  order by sal desc ) 
  where ROWNUM <= 5;

Get last and first record using rank()

; with c as (
    select *,
      rnk = rank() over (partition by Date order by Date ASC),
      rnk2 = rank() over (partition by Date order by Date desc)
    from table
    where SSID= '00921834800'
)
select Date,
City,
Title
from c
where rnk = 1 or rnk2 = 1
order by Date desc

How to get the last row of an Oracle table

There is no such thing as the "last" row in a table, as an Oracle table has no concept of order.

However, assuming that you wanted to find the last inserted primary key and that this primary key is an incrementing number, you could do something like this:

select *
  from ( select a.*, max(pk) over () as max_pk
           from my_table a
                )
 where pk = max_pk

If you have the date that each row was created this would become, if the column is named created:

select *
  from ( select a.*, max(created) over () as max_created
           from my_table a
                )
 where created = max_created

Alternatively, you can use an aggregate query, for example:

select *
  from my_table
 where pk = ( select max(pk) from my_table )

Here's a little SQL Fiddle to demonstrate.

---

Related Topics