Professional Web Applications Themes

Embarrassing! but how to find earliest sales record per customer? - Microsoft SQL / MS SQL Server

Each customer has one or more sales records, and each sales record has a date. I need SQL to deliver one sales record per customer, choosing only the earliest-dated record for each customer. Have tried various combinations of GROUP BY, HAVING, TOP, subqueries etc - it seems so damn simple, but it's eluding me. Many thanks in advance to anyone who can rescue me! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...

  1. #1

    Default Embarrassing! but how to find earliest sales record per customer?


    Each customer has one or more sales records, and each sales record has a
    date.
    I need SQL to deliver one sales record per customer, choosing only the
    earliest-dated record for each customer.

    Have tried various combinations of GROUP BY, HAVING, TOP, subqueries etc
    - it seems so damn simple, but it's eluding me.

    Many thanks in advance to anyone who can rescue me!


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Martyn Guest

  2. #2

    Default Re: Embarrassing! but how to find earliest sales record per customer?

    select *
    from Sales s1
    where s1.sdate = (select min(sdate) from Sales s2 where
    s2.customerid=s1.customerid)

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Martyn Matthews" <co.uk> wrote in message
    news:phx.gbl... 


    oj Guest

  3. #3

    Default Re: Embarrassing! but how to find earliest sales record per customer?

    Here is one guess :

    SELECT *
    FROM tbl
    WHERE datecol = ( SELECT MIN(t1.datecol)
    FROM tbl t1
    WHERE t1.customer = tbl.customer ) ;

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  4. #4

    Default Re: Embarrassing! but how to find earliest sales record per customer?

    Thank you both - amazingly swift answers! Worked a treat.



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Martyn Guest

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