Loading ...
Sorry, an error occurred while loading the content.
 

need an answer for this please!

Expand Messages
  • Yasser Moustafa
    if (typeof YAHOO == undefined ) { var YAHOO = {}; } YAHOO.Shortcuts = YAHOO.Shortcuts || {}; YAHOO.Shortcuts.hasSensitiveText = false;
    Message 1 of 5 , Jan 27, 2009
      if (typeof YAHOO == "undefined") {
      var YAHOO = {};
      }
      YAHOO.Shortcuts = YAHOO.Shortcuts || {};
      YAHOO.Shortcuts.hasSensitiveText = false;
      YAHOO.Shortcuts.sensitivityType = [];
      YAHOO.Shortcuts.doUlt = false;
      YAHOO.Shortcuts.location = "us";
      YAHOO.Shortcuts.document_id = 0;
      YAHOO.Shortcuts.document_type = "";
      YAHOO.Shortcuts.document_title = "i need an answer for this question please!";
      YAHOO.Shortcuts.document_publish_date = "";
      YAHOO.Shortcuts.document_author = "yasse25r@...";
      YAHOO.Shortcuts.document_url = "";
      YAHOO.Shortcuts.document_tags = "";
      YAHOO.Shortcuts.document_language = "english";
      YAHOO.Shortcuts.annotationSet = {

      };
      YAHOO.Shortcuts.headerID = "3cf6c43c408a5d2b1c3825954e0b9e5b";



      what is the difference between the result in these two select statement ......
      i mean in the first statement the result was automatically ordered by empno ascending but in the second one when we added the 'Distinct' keyword the result became different .... here is both statements and their result:
       
       
      1- select mgr , empno from emp;
             MGR      EMPNO
      ---------- ----------
            7902       7369
            7698       7499
            7698       7521
            7839       7566
            7698       7654
            7839       7698
            7839       7782
            7566       7788
                          7839
            7698       7844
            7788       7876
             MGR      EMPNO
      ---------- ----------
            7698       7900
            7566       7902
            7782       7934
      14 rows selected.

      2- select distinct mgr , empno from emp;
             MGR      EMPNO
      ---------- ----------
            7698       7499
            7698       7654
                          7839
            7782       7934
            7902       7369
            7698       7521
            7566       7788
            7566       7902
            7788       7876
            7839       7566
            7839       7782
             MGR      EMPNO
      ---------- ----------
            7839       7698
            7698       7844
            7698       7900
      14 rows selected.





      [Non-text portions of this message have been removed]
    • Arnie Rowland
      According to the ANSI SQL standards, order is not guaranteed UNLESS you specifically request by using ORDER BY. Sometimes, order by appear to be correct ,
      Message 2 of 5 , Jan 27, 2009
        According to the ANSI SQL standards, order is not guaranteed UNLESS you specifically request by using ORDER BY.

        Sometimes, order by appear to be 'correct', perhaps due to a clustered index. But at other times, and other operations, order will be unpredictable.

        ALWAYS use ORDER BY in your queries to have a predictable resultset.

        Regards,

        Arnie Rowland, MVP (SQL Server)

        "Fortune favors the prepared mind." Louis Pasteur

        -----Original Message-----
        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Yasser Moustafa
        Sent: Tuesday, January 27, 2009 7:58 AM
        To: Arnie
        Subject: [SQLQueriesNoCode] need an answer for this please!



        if (typeof YAHOO == "undefined") {
        var YAHOO = {};
        }
        YAHOO.Shortcuts = YAHOO.Shortcuts || {};
        YAHOO.Shortcuts.hasSensitiveText = false;
        YAHOO.Shortcuts.sensitivityType = [];
        YAHOO.Shortcuts.doUlt = false;
        YAHOO.Shortcuts.location = "us";
        YAHOO.Shortcuts.document_id = 0;
        YAHOO.Shortcuts.document_type = "";
        YAHOO.Shortcuts.document_title = "i need an answer for this question please!";
        YAHOO.Shortcuts.document_publish_date = "";
        YAHOO.Shortcuts.document_author = "yasse25r@...";
        YAHOO.Shortcuts.document_url = "";
        YAHOO.Shortcuts.document_tags = "";
        YAHOO.Shortcuts.document_language = "english";
        YAHOO.Shortcuts.annotationSet = {

        };
        YAHOO.Shortcuts.headerID = "3cf6c43c408a5d2b1c3825954e0b9e5b";



        what is the difference between the result in these two select statement .....
        i mean in the first statement the result was automatically ordered by empno ascending but in the second one when we added the 'Distinct' keyword the result became different .... here is both statements and their result:


        1- select mgr , empno from emp;
        MGR EMPNO
        ---------- ----------
        7902 7369
        7698 7499
        7698 7521
        7839 7566
        7698 7654
        7839 7698
        7839 7782
        7566 7788
        7839
        7698 7844
        7788 7876
        MGR EMPNO
        ---------- ----------
        7698 7900
        7566 7902
        7782 7934
        14 rows selected.

        2- select distinct mgr , empno from emp;
        MGR EMPNO
        ---------- ----------
        7698 7499
        7698 7654
        7839
        7782 7934
        7902 7369
        7698 7521
        7566 7788
        7566 7902
        7788 7876
        7839 7566
        7839 7782
        MGR EMPNO
        ---------- ----------
        7839 7698
        7698 7844
        7698 7900
        14 rows selected.





        [Non-text portions of this message have been removed]


        ------------------------------------

        Yahoo! Groups Links







        Disclaimer - January 27, 2009
        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
      • Yasser Moustafa
        what is the difference between the result in these two select statement ...... i mean in the first statement the result was automatically ordered by empno
        Message 3 of 5 , Jan 27, 2009
          what is the difference between the result in these two select statement ......
          i mean in the first statement the result was automatically ordered by empno
          ascending but in the second one when we added the 'Distinct' keyword the
          result became different .... here is both statements and their result:

          1- select mgr , empno from emp;
                 MGR      EMPNO
          ---------- ----------
                7902       7369
                7698       7499
                7698       7521
                7839       7566
                7698       7654
                7839       7698
                7839       7782
                7566       7788
                           7839
                7698       7844
                7788       7876
                 MGR      EMPNO
          ---------- ----------
                7698       7900
                7566       7902
                7782       7934
          14 rows selected.

          2- select distinct mgr , empno from emp;
                 MGR      EMPNO
          ---------- ----------
                7698       7499
                7698       7654
                           7839
                7782       7934
                7902       7369
                7698       7521
                7566       7788
                7566       7902
                7788       7876
                7839       7566
                7839       7782
                 MGR      EMPNO
          ---------- ----------
                7839       7698
                7698       7844
                7698       7900
          14 rows selected.





          [Non-text portions of this message have been removed]
        • Arnie Rowland
          Random selection. As I wrote in my response yesterday (perhaps you didn t get it, or didn t read it, or didn t understand what you read)... According to the
          Message 4 of 5 , Jan 28, 2009
            Random selection.

            As I wrote in my response yesterday (perhaps you didn't get it, or didn't read it, or didn't understand what you read)...

            According to the ANSI SQL standards, order is not guaranteed UNLESS you specifically request by using ORDER BY.

            Sometimes, order by appear to be 'correct', perhaps due to a clustered index. But at other times, and other operations, order will be unpredictable.

            ALWAYS use ORDER BY in your queries to have a predictable resultset.

            Regards,

            Arnie Rowland, MVP (SQL Server)

            "Fortune favors the prepared mind." Louis Pasteur


            -----Original Message-----
            From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Yasser Moustafa
            Sent: Wednesday, January 28, 2009 9:41 AM
            To: Arnie
            Subject: [SQLQueriesNoCode] need an answer for this please!

            what is the difference between the result in these two select statement .....
            i mean in the first statement the result was automatically ordered by empno
            ascending but in the second one when we added the 'Distinct' keyword the
            result became different .... here is both statements and their result:

            1- select mgr , empno from emp;
            MGR EMPNO
            ---------- ----------
            7902 7369
            7698 7499
            7698 7521
            7839 7566
            7698 7654
            7839 7698
            7839 7782
            7566 7788
            7839
            7698 7844
            7788 7876
            MGR EMPNO
            ---------- ----------
            7698 7900
            7566 7902
            7782 7934
            14 rows selected.

            2- select distinct mgr , empno from emp;
            MGR EMPNO
            ---------- ----------
            7698 7499
            7698 7654
            7839
            7782 7934
            7902 7369
            7698 7521
            7566 7788
            7566 7902
            7788 7876
            7839 7566
            7839 7782
            MGR EMPNO
            ---------- ----------
            7839 7698
            7698 7844
            7698 7900
            14 rows selected.





            [Non-text portions of this message have been removed]


            ------------------------------------

            Yahoo! Groups Links







            Disclaimer - January 28, 2009
            This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
            This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
          • GULJEET SINGH
            Hi Yasser, In first statement,Data is displayed as it was stored. But in second statement the distinct keyword will take the first of the row in the
            Message 5 of 5 , Jan 28, 2009
              Hi Yasser,
              In first statement,Data is displayed as it was stored.
              But in second statement the distinct keyword will take the first of the row in the group,that's why it is taking the mgr value first and then empno and displying in sorted form as per mgr.
               
              Regards,
              Guljeet
              --- On Tue, 27/1/09, Yasser Moustafa <yasse25r@...> wrote:

              From: Yasser Moustafa <yasse25r@...>
              Subject: [SQLQueriesNoCode] need an answer for this please!
              To: TOADSQL@yahoogroups.com, SQLQueriesNoCode@yahoogroups.com, Oracle_Fans@yahoogroups.com
              Date: Tuesday, 27 January, 2009, 9:31 PM






              what is the difference between the result in these two select statement ......
              i mean in the first statement the result was automatically ordered by empno
              ascending but in the second one when we added the 'Distinct' keyword the
              result became different .... here is both statements and their result:

              1- select mgr , empno from emp;
                     MGR      EMPNO
              ---------- ----------
                    7902       7369
                    7698       7499
                    7698       7521
                    7839       7566
                    7698       7654
                    7839       7698
                    7839       7782
                    7566       7788
                               7839
                    7698       7844
                    7788       7876
                     MGR      EMPNO
              ---------- ----------
                    7698       7900
                    7566       7902
                    7782       7934
              14 rows selected.

              2- select distinct mgr , empno from emp;
                     MGR      EMPNO
              ---------- ----------
                    7698       7499
                    7698       7654
                               7839
                    7782       7934
                    7902       7369
                    7698       7521
                    7566       7788
                    7566       7902
                    7788       7876
                    7839       7566
                    7839       7782
                     MGR      EMPNO
              ---------- ----------
                    7839       7698
                    7698       7844
                    7698       7900
              14 rows selected.

              [Non-text portions of this message have been removed]
















              Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/

              [Non-text portions of this message have been removed]
            Your message has been successfully submitted and would be delivered to recipients shortly.