Professional Web Applications Themes

ORDER BY Question - Microsoft SQL / MS SQL Server

Hi All, This may be a little difficult to explain but here goes... I have two tables one for companies and one for offices. Threre is potentially many offices to one company. The office table has a field 'Verified_Date' in it. What I need to do is return a distinct list of companies (i.e., only one of each) ordered by the office 'Verified_Date' descending. Where there is more than one office I need to use the most recent 'Verified_Date'. So what I whould end up with is a list of unique companies ordered by the most recent of the offices ...

  1. #1

    Default ORDER BY Question

    Hi All,

    This may be a little difficult to explain but here goes... I have two
    tables one for companies and one for offices. Threre is potentially many
    offices to one company. The office table has a field 'Verified_Date' in it.
    What I need to do is return a distinct list of companies (i.e., only one of
    each) ordered by the office 'Verified_Date' descending. Where there is more
    than one office I need to use the most recent 'Verified_Date'.

    So what I whould end up with is a list of unique companies ordered by the
    most recent of the offices 'Verified_Date's.

    I can't figure out the SQL to do this? Any help much appreciated.

    Thanks,

    Steve


    Steve Guest

  2. #2

    Default Re: ORDER BY Question

    SELECT c.company_name, MAX(o.Verified_Date)
    FROM companies c
    INNER JOIN offices o
    ON c.company_id = o.company_id
    GROUP BY c.company_name
    ORDER BY MAX(o.Verified_Date)

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Steve" <steveayres.net> wrote in message
    news:us4lnMfQDHA.2036TK2MSFTNGP10.phx.gbl...
    > Hi All,
    >
    > This may be a little difficult to explain but here goes... I have two
    > tables one for companies and one for offices. Threre is potentially many
    > offices to one company. The office table has a field 'Verified_Date' in
    it.
    > What I need to do is return a distinct list of companies (i.e., only one
    of
    > each) ordered by the office 'Verified_Date' descending. Where there is
    more
    > than one office I need to use the most recent 'Verified_Date'.
    >
    > So what I whould end up with is a list of unique companies ordered by the
    > most recent of the offices 'Verified_Date's.
    >
    > I can't figure out the SQL to do this? Any help much appreciated.
    >
    > Thanks,
    >
    > Steve
    >
    >

    Jacco Schalkwijk Guest

Similar Threads

  1. Tab Order always greyed out, need to redefine order but can't
    By Dominic_De_Lello@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 5
    Last Post: July 14th, 05:06 AM
  2. ridiculous question about alphabetical order by MACOSX
    By jann_lipka@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 6
    Last Post: April 5th, 06:02 AM
  3. Replies: 1
    Last Post: November 9th, 08:11 PM
  4. stacking order question
    By Anonymoose in forum Macromedia Flash
    Replies: 1
    Last Post: November 3rd, 08:23 PM
  5. Updating records in order (into an order)
    By A Ratcliffe in forum ASP Database
    Replies: 7
    Last Post: October 24th, 06:19 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