Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2009
    Posts
    15

    Unanswered: Distinct or Top value in only one column

    Hi,

    I tried searching the forum for a good answer and had difficulty with the answer. Perhaps someone could help as I'm sure its an easy answer (I think it involves nested selects and creative use of IN and Distinct) but I can not think how to phrase the query.

    My sample database is like follows:

    Pkid | cust_id | date_ordered
    1 100 28 May 09
    2 100 1 Jun 09
    3 108 29 May 09
    4 108 3 Jun 09
    5 108 4 Jun 09

    What I would like to search for would be the first date ordered for each cust_id, returning the following values:

    pkid cust_id date_ordered
    1 100 28 May 09
    3 108 29 May 09

    I've tried using top() which, since I'm a novice, I've incorrectly applied and only get one value. I then tried nesting it within a query and my computer just displays a frowny-face and tells me I need help.

    Any advice, either on how to do this or perhaps a good explanation on how to approach these questions so that I can learn the actual process?

    Thank you all for your help.
    Chris

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a group by clause

    eg
    select top pkid,custid,dateordered from mytable
    group by pkid,dateordered
    order by dateordered desc
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mark, WTF is that?

    OP - What if your data was like this? What would the result be then? (it makes a difference, honest).Change in red

    Pkid | cust_id | date_ordered
    1 100 28 May 09
    2 100 1 Jun 09
    3 108 29 May 09
    4 108 3 Jun 09
    5 108 4 Jun 09
    6 108 1 Jan 09
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2009
    Posts
    15
    If there were another block like the one in red, the result should yield the following:

    PKID cust_id date_ordered
    1 100 28 May 09
    6 108 1 Jan 09

    I'm wishing to return the unique pkid, the cust_id and the date_ordered of the record that chronologically has the earliest date form the date_ordered column.

    I was originally thinking I'd want to sort by cust_id and then order ascending by date_ordered and select the top value.

    MS Access informed me that my original thinking (or its execution) was incorrect.

    And while it isn't important, I am doing this through the SQL portion because its important to learn.



    ---------------------


    OP - What if your data was like this? What would the result be then? (it makes a difference, honest).Change in red

    Pkid | cust_id | date_ordered
    1 100 28 May 09
    2 100 1 Jun 09
    3 108 29 May 09
    4 108 3 Jun 09
    5 108 4 Jun 09
    6 108 1 Jan 09[/QUOTE]

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah! You want to learn? Splendid!

    In that case would you prefer to be guided rather than spoon fed?
    Task #1 - look up "derived tables" if you have not heard of them in SQL before.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually task #2 would be to look up GROUP BY if you don't know that yet too.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2009
    Posts
    15
    Nothing like some good lunch-time reading to make you appreciate how fast an hour can blow past you.

    I have read about group_by as well as nested tables and tried to initially apply the group_by to my example as follows:

    SELECT cust_id, Min([dt_ordered]) AS Date
    FROM Table1
    group by cust_id

    with the following results:
    100 5/28/2009
    108 1/1/2009

    So far, that was pretty neat. of course, group_by seems to be the kind of thing that you think you know just enough to really screw up a query. But so far, so good.

    Now, I am really stuck on how to apply the data to get the pkid as well. My thought is that I would run the above query as the nested query and then attach the results as a join to the original (so that it returns the pkid where both cust_id and date are equal). Is this close, because I'm having difficulty with the syntax.

    My non-functional *attempt* was as follows:

    select t2.pkid, t2.cust_id, t2.dt_ordered
    From
    (SELECT cust_id, Min([dt_ordered]) AS dt
    FROM Table1
    group by cust_id) as t2

    where table1.cust_id=t2.cust_id and table1.date=t2.dt_ordered

    Now, I know that this is probably painful for anyone with experience to read. I posted it to possibly also learn where my thinking is wrong.


    Thanks for your help so far

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Absolutely spot on - good job! The only thing is these are not "nested tables" - those are something else entirely (not available in Access). This is a "derived table", so called since you use it like a table but it is derived (i.e. not a literal table).

    Your SQL is pretty close. Do you know how to join tables? Because if you do then you can fix your SQL. Remember you reference your derived table exactly like you would reference any other table. You could write your SQL as though t2 is a literal table, then just swap in your derived table code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2009
    Posts
    15
    I made two different tables and joined them to learn the code and then tried it again with derived tables (yeah, not nested... my bad). It worked wonderfully.

    Thank you for your help. Very, very much.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Glad it worked
    Post your final code for posterity in case someone else has the same problem
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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