Professional Web Applications Themes

SQL query help - Microsoft SQL / MS SQL Server

Hi I am trying to do a query as follows i have a table with several columns, what i want to do is something like this select distinct (desc), id, enddate from table where id = 12 order by enddate desc unfortunately the distinct seems to work across all columns ie it will pick all rows because the id column is unique so if i have id desc date 1 aaaa 2003/2/10 2 aaaa 2003/4/10 it will return both when i want just the row with id = 2 any help on how to do this ? I am using ...

  1. #1

    Default SQL query help

    Hi

    I am trying to do a query as follows

    i have a table with several columns, what i want to do is something like
    this

    select distinct (desc), id, enddate from table
    where id = 12
    order by enddate desc

    unfortunately the distinct seems to work across all columns ie it will pick
    all rows because the id column is unique

    so if i have

    id desc date
    1 aaaa 2003/2/10
    2 aaaa 2003/4/10

    it will return both when i want just the row with id = 2

    any help on how to do this ?

    I am using Access 2k by the way.

    Silk



    silk Guest

  2. #2

    Default Re: SQL query help

    Hi thanks for this

    - however this only works if you have the same 'aaaa' ie col2
    what if you had this in the table also :

    insert into e values (3,bbb,'20030824')
    insert into e values (4,bbb,'20030825')

    ie what i want is a distinct list of col2's each being the highest dated-
    for eg the lats 'aaa' and the last 'bbb'

    Thanks

    Silk



    "Uri Dimant" <co.il> wrote in message
    news:phx.gbl... 
    > pick 
    >
    >[/ref]


    silk Guest

  3. #3

    Default Re: SQL query help

    This makes no sense. If you select (where ID = 12) it won't return either of
    your rows. If you specify the ID then, if the ID is unique, you'll get only
    one row returned so the 'distinct' is irrelevant.




    "silk" <co.uk> wrote in message
    news:%phx.gbl... 
    pick 


    martinique Guest

  4. #4

    Default Re: SQL query help

    With your given example, clearly this won't work, since you want the
    'distinct' to apply only to some fields but not all.

    Try this:

    Select [desc], ID, Max(enddate) FROM ... GROUP BY [desc], ID



    "silk" <co.uk> wrote in message
    news:phx.gbl... [/ref]
    either 
    > only [/ref][/ref]
    like 
    > > pick 
    > >
    > >[/ref]
    >
    >[/ref]


    Jezebel Guest

  5. #5

    Default Re: SQL query help

    Hi Uri,

    Thanks for your help - however i am having a problem with you solution -

    here is my enterpretation of your answer

    select othercost.* from othercost
    join
    (
    select max(enddate) as col3 , max(othercostid) as col1, [desc] from
    othercost
    group by [desc]
    ) as oc1
    on oc1.[desc] = othercost.[desc] and oc1.propertyid=81


    I get a syntax error the 'join' clause - i am using access 2000

    Silk


    "Uri Dimant" <co.il> wrote in message
    news:phx.gbl... [/ref]
    dated- [/ref]
    > like [/ref][/ref]
    will 
    > >
    > >[/ref]
    >
    >[/ref]


    silk Guest

  6. #6

    Default Re: SQL query help

    Hi Jezebel

    I just came across a problem with your solution - hope you can help with
    this

    if one of the columns has null values then it appears twice in the result
    set for example if i have columns

    id, desc, enddate, somecol

    then i do

    Select [desc], ID, Max(enddate), somecol FROM ... GROUP BY [desc], ID,
    somecol

    if somecol has values then it works fine but if i have

    desc = "fred" id = 1 enddate="2/2/02" somecol="joe"
    and
    desc = "fred" id = 2 enddate="3/4/02" somecol="" - this is empty or null

    then both these rows appear

    anyway i can counter this ?

    TIA

    Silk






    "Jezebel" <net> wrote in message
    news:OC$phx.gbl... [/ref]
    say [/ref]
    > either 
    > > only [/ref]
    > like [/ref][/ref]
    will 
    > >
    > >[/ref]
    >
    >[/ref]


    silk Guest

  7. #7

    Default Re: SQL query help

    That's what grouping is supposed to do: give you one row for each
    combination of grouping values. If you don't want the null values in your
    output then either exclude them from the query

    (WHERE not (somecol is null)

    or do your original selection into a temporary table, then requery from
    there.




    "silk" <co.uk> wrote in message
    news:phx.gbl... [/ref]
    > say 
    > > either [/ref][/ref]
    get [/ref][/ref]
    something [/ref]
    > will 
    > >
    > >[/ref]
    >
    >[/ref]


    Jezebel Guest

  8. #8

    Default Re: SQL query help

    Thanks for your help Jezebel

    the tmp table idea worked a treat

    Silk


    "Jezebel" <net> wrote in message
    news:phx.gbl... [/ref]
    result [/ref][/ref]
    lets [/ref]
    > get [/ref]
    > something [/ref][/ref]
    it 
    > >
    > >[/ref]
    >
    >[/ref]


    silk Guest

Similar Threads

  1. Replies: 5
    Last Post: August 9th, 04: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. 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