Can we use order by in subquery? If not why sometime could use top(n) order by?

Question

I'm an entry level trying to learn more about SQL,

I have a question "can we use order by in subquery?" I did look for some article says no we could not use.

But on the other hand, I saw examples using top(n) with order by in subquery:

select c.CustomerId,
    c.OrderId
from CustomerOrder c
inner join (
    select top 2
    with TIES CustomerId,
        COUNT(distinct OrderId) as Count
    from CustomerOrder
    group by CustomerId
    order by Count desc
    ) b on c.CustomerId = b.CustomerId

So now I'm bit confused.

Could anyone advise?

Thank you very much.

Possible duplicate of [Is order by clause allowed in a subquery](http://stackoverflow.com/questions/2101908/is-order-by-clause-allowed-in-a-subquery) — Vasan, Apr 26 '17 at 17:54

Manav Sharma · Answer 1 · 2017-04-26T18:04:48.467

0

Yes we can use order by clause in sub query, for example i have a table named as product (check the screen shot of table http://prntscr.com/f15j3z). Chek this query on your side and revert me in case of any doubt.

select p1.* from product as p1 where product_id = (select p2.product_id from product as p2 order by product_id limit 0,1)

edited Apr 26 '17 at 18:04

answered Apr 26 '17 at 17:58

Manav Sharma

187
1
8

India.Rocket · Accepted Answer · 2017-04-26T19:36:54.863

0

Yes, you are right we cannot use order by in a inner query. Because it is acting as a table. A table in itself needs to be sorted when queried for different purposes.
In your query itself the inner query is select some records using Top 2. Eventhough these are top 2 records only, they form a table with 2 records which is enough for it to recognized as a table and join it with another table

The right query will be:-

SELECT * FROM
(
    SELECT c.CustomerId, c.OrderId, DENSE_RANK() OVER(ORDER BY b.count DESC) AS RANK

    FROM CustomerOrder c

    INNER JOIN

    (SELECT CustomerId, COUNT(distinct OrderId) as Count
    FROM CustomerOrder   GROUP BY CustomerId) b 

    ON c.CustomerId = b.CustomerId
) a

WHERE RANK IN (1,2);

Hope I have answered your question.

edited Apr 26 '17 at 19:36

answered Apr 26 '17 at 18:02

India.Rocket

1,225
1
8
11

Hi Inian.Rocket, thank you for your reply, love your name btw. If the final out put should only be customerId and OrdeId without count, what should I do? – ADJ Apr 26 '17 at 18:14
@ADJ Just remove count from select, you will still get your answer. I kept it there for your understanding. Just to make sure you can verify and understand results also post getting the output – India.Rocket Apr 26 '17 at 18:17
sorry if I did explain my question well. I would like to list top 2 customerId with maximum orders, your code runs very well but it does not return what I wanted – ADJ Apr 26 '17 at 18:35
@ADJ what are you getting because logic is right. It should give you right results. Check this query with and without TOP 2 TIES statement. If top 2 records are the same then your results are right – India.Rocket Apr 26 '17 at 18:42
yes, the logic is right if we are looking for top 2 combination (customerid and orderid) as a key. For example, we have customer 1,2,3, order number a,b,c. customer 1 has order a,b,c, customer 2 has a,c, customer 3 has b. I would like to find top customer order most, so customer 1 with a,b,c and customer 2 with a,c. – ADJ Apr 26 '17 at 19:11
@ADJ You are using SQL server or my sql? – India.Rocket Apr 26 '17 at 19:19
@ADJ I have fixed it now to give you your required output – India.Rocket Apr 26 '17 at 19:39

score 0 · Answer 3 · answered Apr 26 '17 at 18:06

yes we can use order by in subquery,but it is pointless to use it. It is better to use it in the outer query.There is no use of ordering the result of subquery, because result of inner query will become the input for outer query and it does not have to do any thing with the order of the result of subquery.

Can we use order by in subquery? If not why sometime could use top(n) order by?

3 Answers3