I was perusing an old Chris Date book the other day ... "A Guide to DB2" ... it
was actually my first introduction to SQL ... and came across the following ...
I was all prepared to post this next time Joe ranted and raved about record/row,
field/column ... but thought better of it.
> > Oh no ... you have just given Joe Celko additional ammunition to rant and
> rave
> > against Access. Lord help us all if Joe reads the word "records" in the
> > definition you posted.
> EXCACTLY for this reason I always go twice through any of my posts searching
> for some
> sneaked "record" and "field" and change it to "row" and "column" :)
> However this was direct copy & paste from Access help and I don't want to
> change MS words.
> --
> Ivan Arjentinski
> P.S. I hope Joe is not using Find function with "record" and "field" and
> then paste a prepared rant... :)
> -----------------------------------------------
> Please answer only to the newsgroups.
> I'll not answer any direct emails.
> -----------------------------------------------
> > Ivan,
> > Oh no ... you have just given Joe Celko additional ammunition to rant and
> rave
> > against Access. Lord help us all if Joe reads the word "records" in the
> > definition you posted. Another lecture on (1) it's rows not records, (2)
> Access
> > is the invention of Satan, and (3) how Atlanta programmers get $1250 per
> day to
> > convert Access SQL to "real" SQL (ya know, the type used by "REAL MEN")
> :-)
> > ---------------------------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which
> can be
> > cut and pasted into Query Analyzer is appreciated.
> > > Definition from Access for First() and Last():
> > > "Because records are usually returned in no particular order (unless the
> > > query includes an ORDER BY clause), the records returned by these
> functions
> > > will be arbitrary."
> > > So, it is neither (a) nor (b)... :)
> > > Have fun.
> > > --
> > > Ivan Arjentinski
> > > -----------------------------------------------
> > > Please answer only to the newsgroups.
> > > I'll not answer any direct emails.
> > > -----------------------------------------------
> > > > Daniel,
> > > > You have to define "first". The reason SQL (the language) and SQL
> Server
> > > don't
> > > > have a FIRST function is because by definition a table is an unordered
> set
> > > of
> > > > rows. So the concept of a "first" row is meaningless.
> > > > Usually "first" either means ... (a) row entered first in
> chronological
> > > > sequence, or (b) row with lowest value.
> > > > For (a), you need to have a column of data type datetime (or
> > > smalldatetime) to
> > > > locate the earliest row.
> > > > For (b), SQL (the language) and SQL Server (and Access) support the
> MIN
> > > > function.
> > > > Perhaps you can post which definition you want. It would also help if
> you
> > > would
> > > > post table schemas (CREATE TABLEs), some sample data (INSERTs) and the
> > > output
> > > > you want from the sample data. Code is usually less open to
> > > misinterpretation
> > > > than is a description of a problem.
> > > > ---------------------------------------------------------------
> > > > BP Margolin
> > > > Please reply only to the newsgroups.
> > > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
> which
> > > can be
> > > > cut and pasted into Query Analyzer is appreciated.
> > > > > Does anybody know how to rewrite an Access SQL statement that can
> > > perform
> > > > > the same function as follows?
> > > > > I am having problems with the FIRST expression since SQL doesn't
> have an
> > > > > equilavent....
> > > > > SELECT OrderDetail.DocumentNumber, First(CustomerMaster.Name) AS
> > > Customer,
> > > > > First(CustomerMaster.City) AS City, First(CustomerMaster.State) AS
> > > State,
> > > > > Sum(DeliveryDetail.Cases) AS Cases, Sum(DeliveryDetail.Pounds) AS
> Weight
> > > > > FROM CustomerMaster RIGHT JOIN (OrderDetail LEFT JOIN DeliveryDetail
> ON
> > > > > (OrderDetail.LineItem = DeliveryDetail.LineItem) AND
> > > > > (OrderDetail.DocumentNumber = DeliveryDetail.DocumentNumber)) ON
> > > > > CustomerMaster.CustomerNumber = OrderDetail.SoldToCustomer
> > > > > GROUP BY OrderDetail.DocumentNumber;
by