CODEWITHSUNDEEP
sqloracle-database
Jen
Jen

Reputation: 29

Limit row output in SQL

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?

Upvotes: 0

Views: 6153

Answers (5)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153730

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

Upvotes: 0

Ben Wyatt
Ben Wyatt

Reputation: 409

Change the start of your query to SELECT TOP 2 DISTINCT

Upvotes: 2

vol7ron
vol7ron

Reputation: 42099

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
;

Upvotes: 0

Kuberchaun
Kuberchaun

Reputation: 30314

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;

Upvotes: 1

Oded
Oded

Reputation: 498952

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)

Upvotes: 2

Related Questions