-1

I was wondering how this query is executed:

I got the error message "An ORDER BYclause is not allowed in a derived table." same using TOP.

NOT WORK:

SELECT
    count(*),
    sum(a_metric_1),
    a_fld_1
FROM
    (
        SELECT
            sales AS a_metric_1,
            product AS a_fld_1
        FROM
            bi_sales
    ) sel1
INNER JOIN (
    SELECT
        TOP 10 product AS b_fld_1
    FROM
        bi_sales
    GROUP BY
        product
    ORDER BY
        sum(sales) DESC
) sel2 ON sel1.a_fld_1 = sel2.b_fld_1
GROUP BY
    a_fld_1
ORDER BY
    a_fld_1

SELECT's work individually, but do not work in a derived table.

WORK:

SELECT
    TOP 10 product AS b_fld_1
FROM
    bi_sales
GROUP BY
    product
ORDER BY
    sum(sales) DESC

I need to run SQL using derived table with ORDER BY and TOP functions. Because I work with development software to generate PHP code, it's a RAD. So the user should inform your own SQL, can be simple or complex.

Imagine that "sel1" which may be another SQL complex with JOIN's, CASES ...

Thus, we treat the user SQL as the "sel1". We reply using the TOP ORDER BY with the "sel2" and then make a new external SELECT with comparative JOIN in "sel1" and "sel2".

On Sybase don't work:

SELECT column FROM ( SELECT top 10 column2 FROM table ORDER BY column2 )

The same statement work on MS SQL Server, MySQL, Oracle, MS Access, PostgreSQL, SQLite, Firebird and Informix.

Thanks in advance for your replies !

John L. Santos
  • 13
  • 5

2 Answers2

0

In your case, perhaps this does what you want:

SELECT TOP 10 product AS b_fld_1, SUM(sales), COUNT(*)
FROM bi_sales
GROUP BY product
ORDER BY sum(sales) DESC;

It is much simpler.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think, you should move the order by clause at the outer most query

    SELECT A.column FROM ( SELECT top 10 column2 FROM table ) A
    ORDER BY A.column2
TigaPuluh Saat
  • 79
  • 1
  • 9