Limit row output in SQL

Question

I have this in SQL:

   SELECT DISTINCT 
          O.custid, 
          P.productname, 
          TO_CHAR(MAX((quantity) * D.unitprice) AS "Revenue"
     FROM corp.Orders O 
LEFT JOIN corp.Order_Details D ON O.orderid = D.orderid
LEFT JOIN corp.Products P ON D.productid = P.productid  
 GROUP BY  O.custid, P.productname 
   HAVING P.productname = 'Ctte de Blaye'
 ORDER BY MAX(quantity) * D.unitprice DESC;

It gives:

CUSTID    Productname      revenue
QUICK Ctte de Blaye 15810
HANAR Ctte de Blaye 15810
PICCO Ctte de Blaye 10540
RATTC Ctte de Blaye 10540
SIMOB Ctte de Blaye 10540
MEREP Ctte de Blaye 10329.2
QUEEN Ctte de Blaye 8432
KOENE Ctte de Blaye 7905
GREAL Ctte de Blaye 7905
WHITC Ctte de Blaye 6587.5
SPLIR Ctte de Blaye 4216
ERNSH Ctte de Blaye 4216
BERGS Ctte de Blaye 3952.5
TORTU Ctte de Blaye 3952.5
THEBI Ctte de Blaye 2635
SANTG Ctte de Blaye 2108
BLONP Ctte de Blaye 2108
SPECD Ctte de Blaye 1317.5
RANCH Ctte de Blaye 527

How do I make it return only the first 2 rows?

Hi, Jen. Welcome to SO. Please invest some time in learning how to format your code (hint: ctrl-k). People will be more likely to help you. Best of luck. — mechanical_meat
– mechanical_meat, Commented Oct 8, 2010 at 18:37
For what database? ...or I NEED TO KNOW WHAT DATABASE YOU"RE USING! — OMG Ponies
– OMG Ponies, Commented Oct 8, 2010 at 18:38
TO_CHAR is only supported by PostgreSQL and Oracle--which of the two is are you using? — OMG Ponies
– OMG Ponies, Commented Oct 8, 2010 at 18:46

Oded · Accepted Answer · 2010-10-08 18:46:17Z

2

If using SQL Server 2000 and later, you can add a TOP clause to your query:

SELECT TOP 2 DISTINCT O.custid, P.productname, to_char(Max((quantity)*(D.unitprice))) AS "Revenue"
FROM (corp.Orders O LEFT JOIN corp.Order_Details D ON O.orderid = D.orderid) LEFT JOIN corp.Products P ON D.productid = P.productid  GROUP BY  O.custid, P.productname 
HAVING (((P.productname)='Ctte de Blaye')) 
ORDER BY Max((quantity)*(D.unitprice)) DESC;

There is no standard supported way of doing this across different databases, so knowing which one you are using is essential.

See this page for the many different

SELECT * FROM T LIMIT 10 --PostgreSQL, MySQL, SQLite, H2
SELECT * from T WHERE ROWNUM <= 10 --Oracle (also supports the standard, since Oracle8i)
SELECT FIRST 10 * from T --Ingres
SELECT FIRST 10 * FROM T order by a --Informix
SELECT SKIP 20 FIRST 10 * FROM T order by c, d --Informix (row numbers are filtered after order by is evaluated. SKIP clause was introduced in a v10.00.xC4 fixpack)
SELECT * FROM T FETCH FIRST 10 ROWS ONLY --DB2 (also supports the standard, since DB2 v8)
SELECT TOP 10 * FROM T --MS SQL Server (also supports the standard, since SQL Server 2005), Sybase ASE, MS Access
SELECT TOP 10 START AT 20 * FROM T --Sybase SQL Anywhere (also supports the standard, since version 9.0.1)

edited Oct 8, 2010 at 18:46

answered Oct 8, 2010 at 18:38

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OMG Ponies Over a year ago

Actually, there is ANSI standard (SQL:2008) syntax--FETCH FIRST n ROWS ONLY--it's just not supported by the vendors yet, besides DB2 anyways.

Oded Over a year ago

@OMG Ponies - thanks for the info. Added "No standard supported way" to answer...

Magnus Hagander Over a year ago

@OMG: it's supported in PostgreSQL from version 8.4.

Infinite Recursion · Accepted Answer · 2015-06-18 13:52:26Z

2

Change the start of your query to SELECT TOP 2 DISTINCT

edited Jun 18, 2015 at 13:52

Infinite Recursion

6,57528 gold badges42 silver badges51 bronze badges

answered Oct 8, 2010 at 18:37

Ben Wyatt

4095 silver badges14 bronze badges

2 Comments

OMG Ponies Over a year ago

TOP is SQL Server specific, and SQL Server 2000+ at that.

Ben Wyatt Over a year ago

I'm sorry, I didn't notice the tags at first. It looks like Paul has an Oracle answer, though.

Kuberchaun · Accepted Answer · 2010-10-08 19:10:07Z

1

Let's assume you are running this on PostgreSQL.

Try using LIMIT:

SELECT DISTINCT 
          O.custid, 
          P.productname, 
          TO_CHAR(MAX((quantity) * D.unitprice) AS "Revenue"
     FROM corp.Orders O 
LEFT JOIN corp.Order_Details D ON O.orderid = D.orderid
LEFT JOIN corp.Products P ON D.productid = P.productid  
 GROUP BY  O.custid, P.productname 
   HAVING P.productname = 'Ctte de Blaye'
 ORDER BY MAX(quantity) * D.unitprice DESC
LIMIT 2;

answered Oct 8, 2010 at 19:10

Kuberchaun

30.5k8 gold badges54 silver badges59 bronze badges

Comments

vol7ron · Accepted Answer · 2010-10-08 22:43:08Z

DB2:

SELECT DISTINCT 
            O.custid 
          , P.productname 
          , TO_CHAR(MAX((quantity) * D.unitprice)    AS "Revenue"
FROM        corp.Orders O 
LEFT JOIN   corp.Order_Details D  ON O.orderid   = D.orderid
LEFT JOIN   corp.Products P       ON D.productid = P.productid  
GROUP BY    O.custid
          , P.productname 
HAVING      P.productname = 'Ctte de Blaye'
ORDER BY    MAX(quantity) * D.unitprice DESC
FETCH FIRST 2 ROWS ONLY 
;

Oracle:

SELECT * FROM (
   SELECT DISTINCT 
               O.custid 
             , P.productname 
             , TO_CHAR(MAX((quantity) * D.unitprice)    AS "Revenue"
   FROM        corp.Orders O 
   LEFT JOIN   corp.Order_Details D  ON O.orderid   = D.orderid
   LEFT JOIN   corp.Products P       ON D.productid = P.productid  
   GROUP BY    O.custid
             , P.productname 
   HAVING      P.productname = 'Ctte de Blaye'
   ORDER BY    MAX(quantity) * D.unitprice DESC
)
WHERE ROWNUM < 3;

PostgreSQL:

SELECT DISTINCT 
            O.custid 
          , P.productname 
          , TO_CHAR(MAX((quantity) * D.unitprice)    AS "Revenue"
FROM        corp.Orders O 
LEFT JOIN   corp.Order_Details D  ON O.orderid   = D.orderid
LEFT JOIN   corp.Products P       ON D.productid = P.productid  
GROUP BY    O.custid
          , P.productname 
HAVING      P.productname = 'Ctte de Blaye'
ORDER BY    MAX(quantity) * D.unitprice DESC
LIMIT       2;

SQL Server:

SELECT TOP 2 DISTINCT 
            O.custid 
          , P.productname 
          , TO_CHAR(MAX((quantity) * D.unitprice)    AS "Revenue"
FROM        corp.Orders O 
LEFT JOIN   corp.Order_Details D  ON O.orderid   = D.orderid
LEFT JOIN   corp.Products P       ON D.productid = P.productid  
GROUP BY    O.custid
          , P.productname 
HAVING      P.productname = 'Ctte de Blaye'
ORDER BY    MAX(quantity) * D.unitprice DESC
;

as OMG Ponies stated, TO_CHAR is Postgres/Oracle specific, thus, you'd need to modify that function for SQL Server and DB2

Vlad Mihalcea · Accepted Answer · 2021-01-21 14:51:00Z

The syntax for limiting the result set size depends on the database you are using.

SQL Standard

The SQL:2008 standard defines the following syntax for limiting a SQL query result set:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

The SQL:2008 Top-N records clause is supported in Oracle since 12c, SQL Server since 2012, and PostgreSQL since 8.4.

SQL Server

While SQL Server supports the SQL:2008 Top-N standard syntax, you need to provide the OFFSET clause as well:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
OFFSET 0 ROWS
FETCH FIRST 50 ROWS ONLY

On older SQL Server versions, you can use TOP:

SELECT TOP 50
    title
FROM
    post
ORDER BY
    id DESC

Oracle 11g and older versions

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

MySQL and PostgreSQL 8.3 or older

Traditionally, MySQL and PostgreSQL use the LIMIT clause to restrict the result set to the Top-N records:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
LIMIT 50

Collectives™ on Stack Overflow

Limit row output in SQL

5 Answers 5

3 Comments

2 Comments

Comments

DB2:

Oracle:

PostgreSQL:

SQL Server:

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

2 Comments

Comments

DB2:

Oracle:

PostgreSQL:

SQL Server:

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related