Professional Web Applications Themes

newbie question about "first" - Microsoft SQL / MS SQL Server

hello, i have a table with a list of group_names (varchar) and a list of group_dates (datetime) and a list of group_type_id's (bigint). i want to retrieve a list of group_names, grouped by group_type_id, but i only want to retrieve the EARLIEST group_name (depending on that date column). how can i do this? thanks....

  1. #1

    Default newbie question about "first"

    hello,

    i have a table with a list of group_names (varchar) and a list of
    group_dates (datetime) and a list of group_type_id's (bigint).

    i want to retrieve a list of group_names, grouped by group_type_id, but i
    only want to retrieve the EARLIEST group_name (depending on that date
    column).

    how can i do this?

    thanks.


    suzy Guest

  2. #2

    Default Re: newbie question about "first"

    Can you please post your DDL plus INSERT statements of sample data?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "suzy" <com> wrote in message news:O$phx.gbl...
    hello,

    i have a table with a list of group_names (varchar) and a list of
    group_dates (datetime) and a list of group_type_id's (bigint).

    i want to retrieve a list of group_names, grouped by group_type_id, but i
    only want to retrieve the EARLIEST group_name (depending on that date
    column).

    how can i do this?

    thanks.



    Tom Guest

  3. #3

    Default Re: newbie question about "first"

    No it doesn't work, it has the following errors:

    Column 'group_name' is invalid in the select list because it is not
    contained in either an aggregate function or the GROUP BY clause.
    Column 'dateatecolvalue' is invalid in the HAVING clause because it is not
    contained in either an aggregate function or the GROUP BY clause.


    "Wayne Snyder" <com> wrote in message
    news:phx.gbl... [/ref]

    >
    >[/ref]


    suzy Guest

  4. #4

    Default Re: newbie question about "first"

    sample data is something like this:

    id group_name group_type_id group_date
    =======================================
    1 test group 1 30/09/2003
    2 another test group 1 30/08/2003
    3 yet another one 1 30/10/2003
    4 woo hoo1 2 15/08/2003
    5 woo hoo2 2 15/07/2003
    6 woo hoo3 2 15/06/2003


    the results i want are :

    id group_name group_type_id group_date
    =========================================
    2 another test group 1 30/08/2003
    6 woo hoo3 2 15/06/2003


    i can retrieve the minimum date value by doing min(group_date), but this doesn't work on varchar fields as it returns the first one in alphabetical order, rather than chronological order.





    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Can you please post your DDL plus INSERT statements of sample data?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "suzy" <com> wrote in message news:O$phx.gbl...
    hello,

    i have a table with a list of group_names (varchar) and a list of
    group_dates (datetime) and a list of group_type_id's (bigint).

    i want to retrieve a list of group_names, grouped by group_type_id, but i
    only want to retrieve the EARLIEST group_name (depending on that date
    column).

    how can i do this?

    thanks.
    suzy Guest

  5. #5

    Default Re: newbie question about "first"

    Do:

    SELECT *
    FROM tbl
    WHERE group_dt = ( SELECT MIN(t1.group_dt)
    FROM tbl t1
    WHERE t1.group_type = tbl.group_type ) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  6. #6

    Default Re: newbie question about "first"

    Anith's solution will do the trick.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "suzy" <com> wrote in message news:#phx.gbl...
    sample data is something like this:

    id group_name group_type_id group_date
    =======================================
    1 test group 1 30/09/2003
    2 another test group 1 30/08/2003
    3 yet another one 1 30/10/2003
    4 woo hoo1 2 15/08/2003
    5 woo hoo2 2 15/07/2003
    6 woo hoo3 2 15/06/2003


    the results i want are :

    id group_name group_type_id group_date
    =========================================
    2 another test group 1 30/08/2003
    6 woo hoo3 2 15/06/2003


    i can retrieve the minimum date value by doing min(group_date), but this doesn't work on varchar fields as it returns the first one in alphabetical order, rather than chronological order.





    "Tom Moreau" <spam.me.cips.ca> wrote in message news:phx.gbl...
    Can you please post your DDL plus INSERT statements of sample data?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "suzy" <com> wrote in message news:O$phx.gbl...
    hello,

    i have a table with a list of group_names (varchar) and a list of
    group_dates (datetime) and a list of group_type_id's (bigint).

    i want to retrieve a list of group_names, grouped by group_type_id, but i
    only want to retrieve the EARLIEST group_name (depending on that date
    column).

    how can i do this?

    thanks.

    Tom Guest

  7. #7

    Default Re: newbie question about "first"

    anith, thanks a lot - that works...

    can i ask one more favour please? how would i return records which correspond to the earliest group_name, and the latest group_date (grouped by group type).

    as you can see from the example below this has to combine values from different records.

    ie: sample data is something like this:

    id group_name group_type_id group_date
    =======================================
    1 test group 1 30/09/2003
    2 another test group 1 30/08/2003
    3 yet another one 1 30/10/2003
    4 woo hoo1 2 15/08/2003
    5 woo hoo2 2 15/07/2003
    6 woo hoo3 2 15/06/2003


    the results i want are :

    id group_name group_type_id group_date
    =========================================
    2 another test group 1 30/10/2003
    6 woo hoo3 2 15/08/2003




    "Anith Sen" <com> wrote in message news:phx.gbl... 
    suzy Guest

  8. #8

    Default Re: newbie question about "first"

    Try:

    SELECT id, group_name, group_type_id,
    ( SELECT MAX(t2.group_dt)
    FROM tbl t2
    WHERE t2.group_type_id = tbl.group_type_id )
    FROM tbl
    WHERE group_dt = ( SELECT MIN(t1.group_dt)
    FROM tbl t1
    WHERE t1.group_type_id = tbl.group_type_id ) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

Similar Threads

  1. Newbie question: How can I create a "tiled" background?
    By michaaal in forum Macromedia Flash
    Replies: 2
    Last Post: April 2nd, 06:05 AM
  2. Newbie Question - "trusted SQL Server connection"
    By Greg Smith in forum ASP.NET Security
    Replies: 1
    Last Post: August 20th, 06:43 PM
  3. Replies: 2
    Last Post: July 19th, 08:03 PM
  4. Replies: 0
    Last Post: January 9th, 06:19 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