0

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?

Improve this question
4
  • 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. Commented Oct 8, 2010 at 18:37
  • 3
    For what database? ...or I NEED TO KNOW WHAT DATABASE YOU"RE USING! Commented Oct 8, 2010 at 18:38
  • 2
    And please refrain from ALL CAPS. It is considered rude. Commented Oct 8, 2010 at 18:38
  • 1
    TO_CHAR is only supported by PostgreSQL and Oracle--which of the two is are you using? Commented Oct 8, 2010 at 18:46

5 Answers 5

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

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.
@OMG Ponies - thanks for the info. Added "No standard supported way" to answer...
@OMG: it's supported in PostgreSQL from version 8.4.
2

Change the start of your query to SELECT TOP 2 DISTINCT

Improve this answer

2 Comments

TOP is SQL Server specific, and SQL Server 2000+ at that.
I'm sorry, I didn't notice the tags at first. It looks like Paul has an Oracle answer, though.
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;
Improve this answer

Comments

0

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 ;
Improve this answer

1 Comment

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

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.