Re: MS Access and SQL
Thx. I saw the Access Help file and that didn't really clear things up for me. SQL Server 2000 does not support the FIRST function and I can't find any reference to it in a "standard" SQL book. What the developer was trying to do was to eliminate all duplicate records from a dataset. The Access "Find duplicates" wizard uses the first function on all fields to do this. I thought that in SQL, the DISTINCT function would give me all unique records... Thx - Original Message - From: "Howarth, Craig (IBK-NY)" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, March 20, 2001 10:01 AM Subject: RE: MS Access and SQL > First and Last are aggregate functions in Access SQL, like Max and Min. Not sure if they are supported in other flavors of SQL. > > Here is the explanation from Access Help: > > The First and Last functions are analogous to the MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. 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, they simply return the first value found by the query regardless of order. This is different from DISTINCT which will return all distinct values. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: MS Access and SQL
First and Last are aggregate functions in Access SQL, like Max and Min. Not sure if they are supported in other flavors of SQL. Here is the explanation from Access Help: The First and Last functions are analogous to the MoveFirst and MoveLast methods of a DAO Recordset object. They simply return the value of a specified field in the first or last record, respectively, of the result set returned by a query. 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, they simply return the first value found by the query regardless of order. This is different from DISTINCT which will return all distinct values. > -Original Message- > From: Ken Monroe [SMTP:[EMAIL PROTECTED]] > Sent: Tuesday, March 20, 2001 9:46 AM > To: CF-Talk > Subject: MS Access and SQL > > Hello! > > I've inherited an Access database where someone used the query designer to > create a table based on the following query: > > SELECT Field5, First(Field1), First(Field2), First(Field3),... > FROM Datatable > GROUP BY Field5 > > As near as I can tell, the "First()" function is not standard SQL. I think > that what they were trying to accomplish could be done by saying: > > SELECT DISTINCT Field5, Field1, Field2, Field3,... > FROM Datatable > GROUP BY Field5 (although I think this accomplishes nothing). > > Any ideas? > > Thx > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
MS Access and SQL
Hello! I've inherited an Access database where someone used the query designer to create a table based on the following query: SELECT Field5, First(Field1), First(Field2), First(Field3),... FROM Datatable GROUP BY Field5 As near as I can tell, the "First()" function is not standard SQL. I think that what they were trying to accomplish could be done by saying: SELECT DISTINCT Field5, Field1, Field2, Field3,... FROM Datatable GROUP BY Field5 (although I think this accomplishes nothing). Any ideas? Thx ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
