need first occurance of each date from a table to a new table

#11

yes you are correct I snipped off the last column in the example and I am sorry about that mistake

customerID PurchaseDate
000000011 2008-09-30 00:00:00
000000011 2008-10-30 00:00:00
000000011 2008-03-20 00:00:00
000000022 2008-03-20 00:00:00
000000044 2008-09-30 00:00:00
000000044 2008-10-30 00:00:00
000000044 2008-11-30 00:00:00

This is the records they were wanting I missed cutting and pasting the last column.
For each customer they wanted the unique purchase dates like the example.
so customer 00000011 had 3 dates but customer 000000022 has 6 records but only the 1 date.

The CTE example was the first one I found on the internet and when I tried it it gave me this output.
that is the reason why I posted it.

this line
INSERT newtable (customerID, PurchaseDate, PurchaseID)
was what I was missing I thought it had to go after WHERE Row = 1 ; or it would get all records

you are correct that I am a noob I have been doing access stuff for years and I was going to link this to an access
database where it would have been easy but I decided to try and figure out how to do it in sql.
the sql things I am doing now are pretty blah, just taking data in one database and mapping it to another.
This database has 85 tables 100+ columns each with 80,000 records. Basically a lot of data checking and converting.

customerID of 00000000
order date of 1/1/0952

its been an experience

On Tue, 07 Aug 2012 09:07:46 -0700, Gene Wirchenko <genew (AT) ocis (DOT) net> wrote:

Quote:

On Tue, 7 Aug 2012 08:11:04 +0000 (UTC), Erland Sommarskog
esquel (AT) sommarskog (DOT) se> wrote:

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
On Mon, 06 Aug 2012 23:26:37 +0200, Erland Sommarskog
esquel (AT) sommarskog (DOT) se> wrote:

Gene Wirchenko (genew (AT) ocis (DOT) net) writes:
What am I missing that a CTE is necessary?

To be able to access the value from row_number. Since it is defined in
the SELECT clause, and the SELECT clause is (logically) computed after the
WHERE clause, you cannot refer to the row number in the WHERE clause.

Huh? All he needs is customerID and PurchaseDate. How does my
simple select not do that?

It's unclear what sparks wanted, but from my experience he wants more. And
if he had only wanted those two, he might not have felt compelled to ask.
:-)

It *is* what he said he wanted. As to compelled, he might be a
newbie, and newbies ask some rather obvious questions at times. I
have done that myself (and possibly here recently, too). I hope he
posts again with some more details.

Sincerely,

Gene Wirchenko

#12

I can only say thank you all big time for putting up with my stupid noob questions

thanks again for all your help

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

need first occurance of each date from a table to a new table

microsoft.public.sqlserver.programming microsoft.public.sqlserver.programming

Re: need first occurance of each date from a table to a new table - 08-08-2012 , 12:17 PM

Re: need first occurance of each date from a table to a new table - 08-08-2012 , 12:18 PM