Professional Web Applications Themes

Help about Query - Microsoft SQL / MS SQL Server

GROUP BY is a handy thing. You should read more about it in Books Online, but something liket this should do what you want. select id ,from ,to max(date) from MyTable group id ,from ,to this will give you all unique combo's of id, from, to, and will give the max date associated with each unique combo. -- Brian Moran Principal Mentor Solid Quality Learning SQL Server MVP http://www.solidqualitylearning.com "Johny" <com> wrote in message news:phx.gbl... I have table ID FROM TO Date 1111 2 4 20.10.2002. 1111 2 4 21.12.2002. 2222 4 6 3.3.2003. 3333 6 9 5.5.2003 3333 6 ...

  1. #1

    Default Re: Help about Query

    GROUP BY is a handy thing. You should read more about it in Books Online, but something liket this should do what you want.

    select
    id
    ,from
    ,to
    max(date)
    from
    MyTable
    group
    id
    ,from
    ,to

    this will give you all unique combo's of id, from, to, and will give the max date associated with each unique combo.

    --

    Brian Moran
    Principal Mentor
    Solid Quality Learning
    SQL Server MVP
    http://www.solidqualitylearning.com


    "Johny" <com> wrote in message news:phx.gbl...
    I have table



    ID
    FROM
    TO
    Date

    1111
    2
    4
    20.10.2002.

    1111
    2
    4
    21.12.2002.

    2222
    4
    6
    3.3.2003.

    3333
    6
    9
    5.5.2003

    3333
    6
    9
    6.7.2003

    3333
    6
    9
    8.8.2003

    4444
    9
    10
    9.9.2003




    I want to select data from every ID but ID shouldn't repeat and should have biggest Date.



    Result should be:



    ID
    FROM
    TO
    Date

    1111
    2
    4
    21.12.2002.

    2222
    4
    6
    3.3.2003.

    3333
    6
    9
    8.8.2003

    4444
    9
    10
    9.9.2003




    Is it possible to write such query?

    Brian Guest

  2. #2

    Default Re: Help about Query

    > If I have more columuns which I cant include in GROUP BY, how can I get
    them in result?

    SELECT *
    FROM Sometable AS S
    WHERE [date] =
    (SELECT MAX([date])
    FROM Sometable
    WHERE [id] = S.[id] AND [from] = S.[from] AND [to] = S.[to])

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. Creating data query from mySQL stored query!?
    By johnegbert in forum Coldfusion Database Access
    Replies: 2
    Last Post: August 5th, 09:28 PM
  2. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  3. Convert a query to a list, or find an item in a query
    By Laverda668 in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 7th, 07:41 PM
  4. CAML Query: Multiple Query Fields Issue
    By Jon F. in forum ASP.NET Web Services
    Replies: 0
    Last Post: May 12th, 08:19 PM
  5. Replies: 1
    Last Post: July 2nd, 09:09 AM

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