How to Get Just The First Row in a Result Set After Ordering

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;

SQL Server RANK : get first row from a set of rows

Typically, the fastest method (with the right index) is a correlated subquery:

select 1 as rownum, salary, name, endmonth, name 
from employee e
where e.endmonth = (select max(e2.endmonth)
                    from employee e2
                   );

The index you want is on employee(endmonth).

If you know there will be one row, then order by with fetch first (or your databases equivalent) is the best approach:

select 1 as rownum, salary, name, endmonth, name 
from employee e
order by e.endmonth desc
fetch first one row only;

If your database supports with ties, then you can use that. For instance, in SQL Server:

select top (1) with ties 1 as rownum, salary, name, endmonth, name 
from employee e
order by e.endmonth desc;

How to get a specific row as first result, then the rest of the result set

You can use CASE in the ORDER BY:

SELECT * 
FROM Tests 
Where FileNumber = '111' 
ORDER BY CASE WHEN AppointmentDT='2010-01-01' THEN 0 ELSE 1 END ASC 
    , AppointmentDT DESC

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 only the first row from a java.sql.ResultSet?

For the purpose of just limiting the number of rows in a result set you can do the following:

String yourQuery = "select * from some_table";
PreparedStatement statement = connection.prepareStatement(yourQuery); 
statement.setMaxRows(1); 
rs = statement.executeQuery();

As for a specific class that can hold only one row, this really depends on what you want to do with the data. You could for example just extract the contents of the result set and store them in an array, or if you need names as well as values a map. You could also just read the data into a POJO, if you would rather work with a specific object rather than a generic data structure.

Specify first row in select statement then sort the rest alphabetically

Use two keys in the ORDER BY:

SELECT Categories_Name
FROM Categories
ORDER BY (CASE Categories_Name WHEN 'Select' THEN 1 ELSE 2 END),
         Categories_Name;

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

maintain status in 2 places
preserve status history
...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.