Where can I find definitive SQL documentation.

[gar]

Can anyone tell me where I can find definitive documentation on SQL (or Access' implementation of it)? My question is prompted by my discovery of an instance of the use of the "DISTINCTROW" qualifier (if that's the right word) in a select query and my fruitless attempts to find out precisely how this works (and how, in particular, it's functionality differs from that of "DISTINCT".

[June7]

Review: http://office.microsoft.com/en-us/ac...001032205.aspx

Basically, use DISTINCT and Smith will show only once no matter how many customers are named Smith. But use DISTINCTROW and each customer with last name Smith will result in a record (doesn't have to be joined tables but that seems to be where it would be most appropriate, although I've never encountered a need for DISTINCTROW).

Example dataset of tables joined without DISTINCT or DISTINCTROW.

CustID	Last	First	OrderID
1	Jones	A	123
2	Carson	C	456
3	Smith	M	789
4	Smith	T	654
4	Smith	T	987

SELECT DISTINCT Last FROM query;
returns:
Jones
Carson
Smith

SELECT DISTINCTROW Last FROM query;
returns:
Jones
Carson
Smith
Smith

I would have thought that the OrderID would make every row 'distinct'. I am guessing the programming for DISTINCTROW in the SQL engine is using the PK/FK in conjunction with the Last field to identify 'distinctness'. This means CustID and First fields can be included but OrderID cannot.

[orange]

The links at the bottom relate to Fundamental,intermediate and advanced SQL topics.

The top link is a 2007 version of June7 info.
http://office.microsoft.com/en-ca/access-help/all-distinct-distinctrow-top-predicates-HA001231351.aspx


http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

[alansidman]

This is my goto site for SQL syntax

http://www.w3schools.com/sql/default.asp

[gar]

Thanks to all those who replied; I think I have it sorted out now.

This is my goto site for SQL syntax

http://www.w3schools.com/sql/default.asp