Equivalents to SQL Server Top

Equivalents to SQL Server TOP

To select first 100 rows:

MySQL and PostgreSQL:

SELECT  *
FROM    Table
ORDER BY
        column
LIMIT 100

Oracle:

SELECT  *
FROM    (
        SELECT  t.*
        FROM    table
        ORDER BY
                column
        )
WHERE   rownum <= 100

Note that you need a subquery here. If you don't add a subquery, ROWNUM will select first 10 rows in random order and then sort them by column.

To select rows between 100 and 300:

MySQL:

SELECT  *
FROM    TABLE
ORDER BY
        column
LIMIT   100, 200

PostgreSQL:

SELECT  *
FROM    Table
ORDER BY
        column
OFFSET 100 LIMIT 200

Oracle:

SELECT  *
FROM    (
        SELECT  t.*, ROW_NUMBER() OVER (ORER BY column) AS rn
        FROM    table
        )
WHERE   rn >= 100
        AND rownum <= 200

Note that an attempt to simplify it with ROWNUM BETWEEN 100 AND 200 (as opposed to rn BETWEEN 100 AND 200 in the outer query) will return nothing in Oracle!

RN BETWEEN 100 AND 200 will work in Oracle too but is less efficient.

See the article in my blog for performance details:

• Oracle: ROW_NUMBER vs ROWNUM

Equivalent of SQL Server's TOP 1 in Oracle (without using rownum or row_number())

Oracle does not support TOP 1, as you pointed out. You might be able to rewrite in Oracle while maintaining the correlated subquery, but the best option would probably be to remove that subquery, and instead just use the join you already making to handle the logic:

WITH cte AS (
    SELECT
        PA.*,
        COALESCE(CI.DISCOUNTCODE, 'NA') AS DISCOUNTCODE,
        ROW_NUMBER() OVER (PARTITION BY CI.ID ORDER BY CI.DATE DESC) rn
    FROM PAYMENT PA
    LEFT JOIN CONTRACTINFO CI
        ON PA.CONTRACTID = CI.ID AND
           CI.DATE < PA.PAYMENTDATE 
    WHERE
        CI.BASERECORD = 1
)

SELECT CONTRACTID, TIME, PAYMENTDATE, DISCOUNTCODE
FROM cte
WHERE rn = 1;

SQL Server : is there any alternate for TOP clause?

You can use ROW_NUMBER(), the row_number() is not assigned until you query the data so if you want to retrieve the data by the row_number() value, then you will need to use CTE or a sub-select to get the value for use in a WHERE clause.

SELECT *
FROM
(
  select  exe.id,
        bat.BName,
        bat.tid,
        bat.freq,
        exe.status,
        exe.Msg,
        exe.time,
        exe.Fi,
        row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
                   exe.status, exe.id DESC) rn
  from XXX exe,
       YYY bat 
  where exe.id=bat.id  
) x
WHERE rn between 101 and 200

I would also suggest a change in your JOIN syntax to use ANSI syntax:

SELECT *
FROM
(
  select  exe.id,
        bat.BName,
        bat.tid,
        bat.freq,
        exe.status,
        exe.Msg,
        exe.time,
        exe.File,
        row_number() over(order by CONVERT(VARCHAR(10), exe.time, 120) DESC,
                   exe.status, exe.id DESC) rn
  from XXXexe
  INNER JOIN  YYY bat 
     ON exe.id=bat.id  
) x
WHERE rn between 101 and 200

Alternative SQL ANSI for TOP WITH TIES

Here is a third option for SQL Server:

WITH cte AS (
    SELECT p.id_category, COUNT(*) AS cnt
    FROM product p 
    INNER JOIN category c
        ON p.id_category = c.id_category
    GROUP BY p.id_category
)

SELECT *
FROM cte
WHERE cnt = (SELECT MAX(cnt) FROM cte);

If you also cannot rely on CTEs being available, you can easily enough just inline the CTE into the query. From a performance point of view, DENSE_RANK would probably outperform my answer.

With the CTE removed this becomes:

SELECT *
FROM
(
    SELECT p.id_category, COUNT(*) AS cnt
    FROM product p 
    INNER JOIN category c
        ON p.id_category = c.id_category
    GROUP BY p.id_category
)
WHERE cnt = (SELECT MAX(cnt) FROM (
                 SELECT p.id_category, COUNT(*) AS cnt
                 FROM product p 
                 INNER JOIN category c
                     ON p.id_category = c.id_category
                 GROUP BY p.id_category
            ));

This query would even run on MySQL. As you can see, the query is ugly, which is one reason why things like CTE and analytic functions were introduced into the ANSI standard.

what is the select top equivalent in oracle10g

Assuming that you are inserting data with an incrementing value for the primary key (via a sequence or a date/timestamp value) then you can do:

SELECT *
FROM   (
  SELECT *
  FROM   table_name
  ORDER BY primary_key_column
)
WHERE ROWNUM <= 10;

You need the outer query as the order in which execution happens in a query is:

1. The WHERE clause filters are applied (and the ROWNUM pseudo-column is generated for each row that matches all the WHERE clause filters);
2. Then the ORDER BY clause is applied.

Applying this in a single query will get the first 10 rows found in the database and will then order those rows by the primary key (not what you want). Using an inner query you can force the ORDER BY clause to be applied first and then the filtering on the required number of rows will occur subsequently in the execution of the outer query.

If you don't have an incrementing primary key then you will have to rely on just ROWNUM without any ORDER BY clause - however, if the table has row movement enabled or if you delete a row and then insert a different row (in which case the database may fill the empty space with the new row) then you may find rows are not retrieved in insertion order. If you can live with this then just do:

SELECT *
FROM   table_name
WHERE  ROWNUM <= 10;

LINQ to Entities equivalent of sql TOP(n) WITH TIES

var query = (from q in list.OrderByDescending(s => s.Score).Take(3).Select(s => s.Score).Distinct()
             from i in list
             where q == i.Score
             select i).ToList();

Edit:

@Zefnus

I wasn't sure in which order you wanted it but to change the order you can put a OrderBy(s => s.Score) between select i and ToList()

I don't have the possibility to check what sql statement my linq clause would produce. But your answer is much better i think. And your question was also really good. I never thought about top with ties in linq. ;)

Basically it only takes top 3 scores from the first list and compares them with the whole list and i takes only those scores which are equal to the scores of the first list.

PHP SQL Server Alternative for TOP and LIMIT of MYSQL

Look at these posts. Simulating LIMIT from MySQL is little bit tricky.

LIMIT 10..20 in SQL Server

How to implement LIMIT with Microsoft SQL Server?

From SQL server 2012 there is OFFSET and FETCH.

SELECT * FROM Table ORDER BY FirstName OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

It should be equivalent to LIMIT 10, 5.

There is any equivalent for TOP and BOTTOM in SQL that available in influxdb?

Using window functions is probably the most versatile way to do this:

select *
from (
   select t.*, 
          dense_rank() over (partition by ??? order by ??? asc) as rnk
   from the_table t
) x
where x.rnk = 3; --<< adjust here 

Rows in a relational database have no implied sort order. So "top" or "bottom" only makes sense if you also provide an order by. From your question is completely unclear what that would be.

Using order by .. asc returns the "bottom rows", using order by .. desc returns the "top rows"

If you want top/bottom for the entire table (instead of one row "per group"), the leave out the partition by

dense_rank() will return multiple rows with the same "rank" when the rows have the same highest (or lowest) value in the column you are sorting by. If you don't want that (and pick an arbitrary one from those "duplicates") then use row_number() instead.

---

Related Topics