Select Top N Records Ordered by X, But Have Results in Reverse Order

Select Top N Records Ordered by X, But Have Results in Reverse Order

SELECT * FROM 
   (SELECT TOP 10 * FROM FooTable ORDER BY X DESC) as myAlias 
ORDER BY X ASC 

i.e. you might need an alias on your subquery, but other than that it should work.

How to select top (5) rows with specified Id and latest date?

As your DateOld_ field is of type ntext, you cannot do an order by on that. To sort the results based on that column, you need to convert the values in to datetime format.

You can execute the following query to fetch 5 rows with id_utente = 134 and sorted in descending order as per the time:

select top 5 * from table1 
   where id_utente = 134 
   order by convert(datetime, convert(varchar, dateold_)) desc;

SQLFiddle Example

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;

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

Select top and bottom rows

Using a union is the only thing I can think of to accomplish this

select * from (select top(5) * from logins order by USERNAME ASC) a
union
select * from (select top(5) * from logins order by USERNAME DESC) b

How to get top n companies from a data frame in decreasing order

head and tail are really useful functions!

head(sort(Forbes2000$profits,decreasing=TRUE), n = 50)

If you want the first 50 rows of the data.frame, then you can use the arrange function from plyr to sort the data.frame and then use head

library(plyr)

head(arrange(Forbes2000,desc(profits)), n = 50)

Notice that I wrapped profits in a call to desc which means it will sort in decreasing order.

To work without plyr

head(Forbes2000[order(Forbes2000$profits, decreasing= T),], n = 50)

SQL - Find max value and then corresponding column

You can use row_number()

select city from
(
select *, row_number() over(partition by name order by value desc) as rn
from tablename
)A where rn=1 and name='John'

Alternatively,

select city from tablename t 
where name='John' and value = (select max(value) from tablename t1 where t.name=t1.name)

How to select bottom most rows?

SELECT
    columns
FROM
(
     SELECT TOP 200
          columns
     FROM
          My_Table
     ORDER BY
          a_column DESC
) SQ
ORDER BY
     a_column ASC

MySQL: Select top n max values?

If you do:

select *
from t
order by value desc
limit N

You will get the top N rows.

If you do:

select *
from t join
     (select min(value) as cutoff
      from (select value
            from t
            order by value
            limit N
           ) tlim
    ) tlim
    on t.value >= tlim;

Or you could phrase this a bit more simply as:

select *
from t join
     (select value
      from t
      order by value
      limit N
    ) tlim
    on t.value = tlim.value;

The following is conceptually what you want to do, but it might not work in MySQL:

select *
from t
where t.value >= ANY (select value from t order by value limit N)

How to select first 'N' records from a database containing million records?

If your purpose is to find 100 random rows and sort them afterwards then Lasse's solution is correct. If as I think you want the first 100 rows sorted by name while discarding the others you would build a query like this:

SELECT * 
  FROM (SELECT * 
          FROM myTable 
         WHERE SIZE > 2000 ORDER BY NAME DESC) 
 WHERE ROWNUM <= 100

The optimizer will understand that it is a TOP-N query and will be able to use an index on NAME. It won't have to sort the entire result set, it will just start at the end of the index and read it backwards and stop after 100 rows.

You could also add an hint to your original query to let the optimizer understand that you are interested in the first rows only. This will probably generate a similar access path:

SELECT /*+ FIRST_ROWS*/* FROM myTable WHERE SIZE > 2000 ORDER BY NAME DESC

Edit: just adding AND rownum <= 100 to the query won't work since in Oracle rownum is attributed before sorting : this is why you have to use a subquery. Without the subquery Oracle will select 100 random rows then sort them.

---

Related Topics