Professional Web Applications Themes

Question about the behavior of DISTINCT - Microsoft SQL / MS SQL Server

Hello all, I have a question about using the DISCTINCT Keyword. Does anyone know which record it takes... the first occurence? If so, does it still exhibit his behavior over a sorted table? For example, if one finds him/herself in a situation where one must select one record from the many side of a one to many relationship, and this selection depends on a field on the many side, can one sort by this field and SELECT DISTINCT on the foreign key (we're assuming the absense of a primary key) For example: One-side table: Student_ID, <other student-related fields> Many-side table: ...

  1. #1

    Default Question about the behavior of DISTINCT

    Hello all,
    I have a question about using the DISCTINCT Keyword. Does anyone know which
    record it takes... the first occurence? If so, does it still exhibit his
    behavior over a sorted table?

    For example, if one finds him/herself in a situation where one must select
    one record from the many side of a one to many relationship, and this
    selection depends on a field on the many side, can one sort by this field
    and SELECT DISTINCT on the foreign key (we're assuming the absense of a
    primary key)

    For example:

    One-side table:
    Student_ID, <other student-related fields>

    Many-side table: (student test results)
    Student_ID, Test_Type, Test_Date, Overall_Score, ytical_Score, etc.

    If I wanted to get the most recent test scores, could I sort by the date
    field and SELECT DISTINCT Student_ID?
    Or the highest score, sort by the Overall_Score and SELECT DISTINCT
    Student_ID?

    Thanks for any advice you can give,
    Jason


    Jason Guest

  2. #2

    Default Re: Question about the behavior of DISTINCT

    Jason,

    select distinct returns distinct rows. If there are duplicates,
    select distinct returns exactly one of the duplicates, and it
    doesn't make sense to wonder which one it returns, since they
    are all identical.

    For example, if I

    select distinct left(entry,1,1) as firstLetter
    from BigDictionary

    what do you mean to ask whether the result row

    A

    is from Aardvark, Aspidistra, or Antimacassar?

    If you select distinct Student_ID, you will get a list of Student_ID
    values with no duplicates. The earliest occurence of Student_ID
    2302938 is indistinguishable from any other, so what does it matter
    which row the values 2302938 came from?

    -- Steve Kass
    -- Drew University
    -- Ref: C55AF070-EB54-4CD9-A13E-DBF817D69412

    Jason Carlson wrote:
     

    Steve Guest

  3. #3

    Default Re: Question about the behavior of DISTINCT

    >> I have a question about using the DISTINCT Keyword. Does anyone know
    which record [sic] it takes... the first occurence? <<

    You don't understand that a row is not a record and that a talbe is not
    a file. There is no concept in SQL of a first record -- that has
    meaning only in a sequental file.
     [/ref]

    Thre is no such thing as a "sorted table" -- you can convert a table
    into a cursor. A cursor is a sequential file structure created from the
    result set of a SELECT statement.
     [/ref]
    date field [sic] and SELECT DISTINCT Student_ID? <<

    And a column is not a field -- fields exist in records. This is not a
    sequential file system.

    Here is how a SELECT works in SQL ... at least in theory. Real products
    will optimize things when they can.

    a) Start in the FROM clause and build a working table from all of the
    joins, unions, intersections, and whatever other table constructors are
    there. The table expression> AS <correlation name> option allows you
    give a name to this working table which you then have to use for the
    rest of the containing query.

    b) Go to the WHERE clause and remove rows that do not pass criteria;
    that is, that do not test to TRUE (reject UNKNOWN and FALSE). The WHERE
    clause is applied to the working in the FROM clause.

    c) Go to the optional GROUP BY clause, make groups and reduce each
    group to a single row, replacing the original working table with the new
    grouped table. The rows of a grouped table must be group
    characteristics: (1) a grouping column (2) a statistic about the group
    (i.e. aggregate functions) (3) a function or (4) an expression made up
    of the those three items.

    d) Go to the optional HAVING clause and apply it against the grouped
    working table; if there was no GROUP BY clause, treat the entire table
    as one group.

    e) Go to the SELECT clause and construct the expressions in the list.
    This means that the scalar subqueries, function calls and expressions in
    the SELECT are done after all the other clauses are done. The AS
    operator can give a name to expressions in the SELECT list, too. These
    new names come into existence all at once, but after the WHERE clause,
    GROUP BY clause and HAVING clause has been executed; you cannot use them
    in the SELECT list or the WHERE clause for that reason.

    If there is a SELECT DISTINCT, then redundant duplicate rows are
    removed. For purposes of defining a duplicate row, NULLs are treated as
    matching (just like in the GROUP BY).

    f) Nested query expressions follow the usual scoping rules you would
    expect from a block structured language like C, Pascal, Algol, etc.
    Namely, the innermost queries can reference columns and tables in the
    queries in which they are contained.

    Please get a book on basic relational database theory -- you simply do
    not understand a set-oriented, non-procedural model of data right now.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  4. #4

    Default Re: Question about the behavior of DISTINCT

    Thank you much for your help, I'm trying to avoid the use of a cursor; I
    think your TOP 1 with mutilple SELECTs will do the job!
    Jason Carlson

    "Shinichi Yoneda" <ne.jp> wrote in message
    news:%phx.gbl... 
    > which [/ref]
    his [/ref]
    select [/ref]
    field 
    >[/ref]


    Jason Guest

Similar Threads

  1. behavior question
    By Sylvain & Jen in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: March 14th, 04:14 PM
  2. Swap Image Behavior Question
    By lrains webforumsuser@macromedia.com in forum Macromedia Fireworks
    Replies: 1
    Last Post: October 8th, 12:21 AM
  3. [PHP-DEV] Exception behavior question
    By Hans Lellelid in forum PHP Development
    Replies: 0
    Last Post: September 24th, 12:56 PM
  4. Insert Behavior Question??
    By Kory in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 10th, 03:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139