locked
How can i leave a selection in sql as is ? RRS feed

  • Question

  • User1253338400 posted
    Hi

    In relation to this thread:

    https://forums.asp.net/t/2159465.aspx?How+can+i+do+a+self+join+on+a+table+

    Since the order matters how can i get the result such that it is output as selected.?
    That is you must create and account first , then a contact then an opportunity.
    I know by furst selecting what is first ..

    Opportunity -->account
    Account-->contact

    So account first contact second.

    I cant go off ordering by alphabet as i may have a scenerio where

    Opportunity -->user
    Account-->contact
    So i must create user before a contact.

    Thanks
    Sunday, September 8, 2019 9:12 PM

Answers

  • User303363814 posted

    SQL does not have the concept of a 'natural' order by design.  Any database server is allowed to return rows in any order, different from one query to the next even.  In SQL the result of a select (without the ORDER BY clause) is a SET which is inherently unordered - that's part of the definition of a SET.

    If you want to get rows in a particular order then you have to specify the order using the ORDER BY clause.  There is no such thing as 'keep the order' because there is no order guaranteed.

    One way would be to have a very small table (called, say, NamePriorities) with two columns. One column has each of the possible 'NAME' values and the other column is the Priority of that name.  When you do your select query you would also join with the NamePriorities table.  Now the result can be sorted by the value of the Priority column.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 8, 2019 11:42 PM

All replies

  • User303363814 posted

    SQL does not have the concept of a 'natural' order by design.  Any database server is allowed to return rows in any order, different from one query to the next even.  In SQL the result of a select (without the ORDER BY clause) is a SET which is inherently unordered - that's part of the definition of a SET.

    If you want to get rows in a particular order then you have to specify the order using the ORDER BY clause.  There is no such thing as 'keep the order' because there is no order guaranteed.

    One way would be to have a very small table (called, say, NamePriorities) with two columns. One column has each of the possible 'NAME' values and the other column is the Priority of that name.  When you do your select query you would also join with the NamePriorities table.  Now the result can be sorted by the value of the Priority column.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 8, 2019 11:42 PM
  • User1253338400 posted

    Thanks I will look at implementing the NamePriorities table.

    Monday, September 9, 2019 12:17 AM