Search code examples
sqloracle-databaserownum

select row of minimum value without using rownum

I'm using Oracle SQL and i need some help with a query.

In the following query i'm selecting some rows with a simple condition (never mind hat kind of). From the output rows, i need to select the row with minimum value of DATE. For that, i'm using ROWNUM.

SELECT * 
FROM(
  SELECT NAME, DATE
  FROM LIST
  WHERE NAME = 'BLABLA'
  ORDER by DATE)
WHERE ROWNUM = 1;

However, this query must fit to any other SQL languages, and therefore i need to write this query without ROWNUM. Is there a simple way to write this query without using ROWNUM?

Solution

  • Unfortunately, row limit syntax differs between RDBMS.

    The following is portable between SqlServer, Oracle and PostGres:

    SELECT * 
FROM (
  SELECT NAME, DATE, ROW_NUMBER() OVER (ORDER by DATE) AS RowNum
  FROM LIST
  WHERE NAME = 'BLABLA'
) X
WHERE RowNum = 1;

    However, other DB's syntax is different, e.g. MySql's LIMIT