limiting row output in sql

Question

I have this code 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;

this returns

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

I NEED TO KNOW HOW TO GET THIS ONLY RETURN THE FIRST 2 ROWS!

Answer 1

Change the start of your query to SELECT TOP 2 DISTINCT

Answer 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)

Answer 3

Oracle answer here

Answer 4

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;

Answer 5

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
;