February 17, 2021 at 8:09 pm
Is there a tutorial for this? And can someone help me with what is wrong?
This woks fine if I remove the MAX and GROUP BY. I don't understand what I need to group by.
ERROR: Msg 8120, Level 16, State 1, Line 27
Column 'Documents.DocumentID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT D.DocumentID AS [Document ID], MAX(XR.RevNr) AS [Rev Nr], XR.XRefDocument AS [Child Document ID]
FROM Documents AS D
INNER JOIN
XRefs AS XR ON D.DocumentID = XR.DocumentID
WHERE (D.Deleted = 0) AND (D.DocumentID = 366907)
GROUP BY XR.XRefDocument
Thanks!
Steve Anderson
February 17, 2021 at 8:22 pm
Got it:
But if you have a good tutorial on INNER Joins and Group By's it would be appreciated.
SELECT D.DocumentID AS [Document ID], XR.XRefDocument AS [Child Document ID], MAX(XR.RevNr) AS [Rev Nr]
FROM Documents AS D
INNER JOIN
XRefs AS XR ON D.DocumentID = XR.DocumentID
WHERE (D.Deleted = 0) AND (D.DocumentID = 366907)
GROUP BY D.DocumentID, XR.XRefDocument
ORDER BY XR.XRefDocument
Thanks!
Steve Anderson
February 18, 2021 at 1:44 pm
I'd suggest reading the Stairways series here on SQL Server Central. The introduction to T-SQL stairway has a lesson plan on GROUP BY. Scattered throughout the second t-sql stairway there's a bunch more.
You've already got the basics. Any column in the SELECT list must either be included in an aggregation or included in the GROUP BY clause.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2021 at 3:51 pm
ERROR: Msg 8120, Level 16, State 1, Line 27
Column 'Documents.document_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT D.document_id, XR.xref_document_id, MAX(XR. rev_nbr) AS rev_nbr_max
FROM Documents AS D, XRefs AS XR
WHERE D.document_id = XR.document_id
AND D.document_id = '366907'
GROUP BY XR.xref_document_id;
SELECT D.DocumentID AS [Document ID], MAX(XR.RevNr) AS [Rev Nr], XR.XRefDocument AS [Child Document ID]
FROM Documents AS D
INNER JOIN
XRefs AS XR ON D.DocumentID = XR.DocumentID
WHERE (D.Deleted = 0) AND (D.DocumentID = 366907)
GROUP BY XR.XRefDocument;
I'm going to assume that you are sincere about wanting to learn how to do this right. Find a lot of stuff I'm going to give you in my books, where you can check out the references to the research behind all of this.
1) why would you alias a column to its own name? It's purely a waste of resources and leads to confusion.
2) various languages and ISO standards are case-sensitive, case insensitive, forbid embedded spacing or allow embedded spacing. This is why you find most of the ISO standards using an underscore in data element names; it's guaranteed to be portable across the hundreds (thousands?) conventions in use in this trade.
3) the use of square brackets instead of the double quotes of the ANSI/ISO standards tells the world you are a hillbilly who doesn't know the standards and never expects his coat to leave his one particular product. In the modern IT shop, you're going to find many different versions of SQL and you want to write the most portable code possible. This is another professional versus amateur kind of thing.
4) there is a long and elaborate story as to how we got the infix join notation when I was on the ANSI committee. It is required for outer joins in some other special situations. However, after 30 years of doing this, I found that people who write with infix joins have trouble thinking in sets. Basically, it's the people who would use a capital Sigma for summation (general, high-level abstraction) versus those who write a string of plus signs (linear infixed operations like arithmetic). It's not wrong, but I found it to be a good measure of how weak someone's SQL is.
5) your generic nonspecific "deleted" flag is from assembly language and has no business in a language that's based on logical predicates. If documents have a lifetime, then they need (creation date, deletion date,) pair to show this. Assembly language and old filesystems are not very good at this; it's one of the major reasons for databases.
6) identifiers are always on a nominal scale, by definition. This means they do not measure a quantity, direction, magnitude, etc. so they can't be numeric. Have you taken the time to get a good data modeling course?
7) it's already been pointed out to you that having both a cross-reference and a document ID is wrong because it violates the definition of a grouping. However, there's a more fundamental problem. Cross-referencing is usually a relationship, not a class of entities. This means that we ought to have a cross-reference relationship table somewhere in this schema that joined different documents together.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply