Post reply

subquery

  • August 22, 2012 at 2:38 pm

    #260984

    hi

    can we user order by inside subquery?

  • August 22, 2012 at 2:44 pm

    #1528932

    It's simple enough to test. Why don't you try it and report back the results?

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • August 23, 2012 at 12:20 am

    #1529049

    You can use order by in sub query with TOP 100 PERCENT as sub query is treated as inline view

    IE:

    SELECT * FROM Tbale

    WHERE ColumnName IN

    (SELECT TOP 100 PERCENT ColumnName FROM Tbale ORDER BY ColumnName )

    🙂

  • August 23, 2012 at 12:34 am

    #1529054

    why not?

    ----------
    Ashish

  • August 23, 2012 at 12:57 am

    #1529059

    Try this one:

    select * from Table1(nolock) T1 where T1.column1 in (select T2.column1 from Table2(nolock) T2 order by T2.column1 desc)

    Regards,

    Sumit Rastogi

  • August 23, 2012 at 1:18 am

    #1529063

    harri.reddy (8/22/2012)

    hi

    can we user order by inside subquery?

    Hi Harri,

    As suggested by drew give it a try and you will come to know your own the result and the solution for that as well.:-)

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

  • August 23, 2012 at 1:37 am

    #1529071

    Please also see many other posts for discussions on the dangers of nolock hints.

    Mike

  • August 23, 2012 at 3:08 am

    #1529125

    Sumit Rastogi (8/23/2012)

    Try this one:

    select * from Table1(nolock) T1 where T1.column1 in (select T2.column1 from Table2(nolock) T2 order by T2.column1 desc)

    Regards,

    Sumit Rastogi

    Hi Sumit,

    It is the best practice to avoid nolock. It can provide you incorrect data.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
     🙂

  • August 23, 2012 at 4:38 am

    #1529158

    You can with a TOP statement, but unless you have a row-limiting top SQL may very well just ignore the order by as there's no purpose for it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply