no group by or distinct in SQL Server?!

[Desolator144]

It seems that SQL Server that comes with VS 05 has never heard of DISTINCT clause. Normally it's DISTINCT(columnname) but it has no idea what that means. And the other function that would get rid of duplicates, GROUP BY doesn't work either. I put it in and it just says error by the word group. I wrote the whole statement a little different and it says "Column dbo.tblResults.UserID is invalid in the select list because it is not contained in either an aggregate function or the group by clause" and of course it says that when I put it in the group by clause and it's also in the select clause too so that's a stupid error message. Here's the two statements that don't work for some reason. If anyone knows why, that'd be great:

SELECT TOP (100) PERCENT dbo.tblResults.UserID, dbo.tblUser.UserName, dbo.tblQuiz.Description
FROM dbo.tblResults INNER JOIN
dbo.tblUser ON dbo.tblResults.UserID = dbo.tblUser.UserID INNER JOIN
dbo.tblQuiz ON dbo.tblResults.QuizID = dbo.tblQuiz.QuizID
WHERE (dbo.tblUser.IsInstructor = 'false')
GROUP BY dbo.tblResults.UserID
ORDER BY dbo.tblResults.UserID, dbo.tblQuiz.Description



SELECT TOP (100) PERCENT DISTINCT(dbo.tblResults.UserID), dbo.tblUser.UserName, dbo.tblQuiz.Description
FROM dbo.tblResults INNER JOIN
dbo.tblUser ON dbo.tblResults.UserID = dbo.tblUser.UserID INNER JOIN
dbo.tblQuiz ON dbo.tblResults.QuizID = dbo.tblQuiz.QuizID
WHERE (dbo.tblUser.IsInstructor = 'false')
ORDER BY dbo.tblResults.UserID, dbo.tblQuiz.Description

[si_the_geek]

Is there any reason for "TOP (100) PERCENT"?

I'm pretty sure that Distinct needs to be applied to all fields in the query, not just one of them.

In order to use Group By, all of the fields in the select list need to be in the Group By clause (but not calculated values, such as Sum or Count).

Try this:

SELECT dbo.tblResults.UserID, dbo.tblUser.UserName, dbo.tblQuiz.Description
FROM dbo.tblResults INNER JOIN
dbo.tblUser ON dbo.tblResults.UserID = dbo.tblUser.UserID INNER JOIN
dbo.tblQuiz ON dbo.tblResults.QuizID = dbo.tblQuiz.QuizID
WHERE (dbo.tblUser.IsInstructor = 'false')
GROUP BY dbo.tblResults.UserID, dbo.tblUser.UserName, dbo.tblQuiz.Description
ORDER BY dbo.tblResults.UserID, dbo.tblQuiz.Description

or this:

SELECT DISTINCT dbo.tblResults.UserID, dbo.tblUser.UserName, dbo.tblQuiz.Description
FROM dbo.tblResults INNER JOIN
dbo.tblUser ON dbo.tblResults.UserID = dbo.tblUser.UserID INNER JOIN
dbo.tblQuiz ON dbo.tblResults.QuizID = dbo.tblQuiz.QuizID
WHERE (dbo.tblUser.IsInstructor = 'false')
ORDER BY dbo.tblResults.UserID, dbo.tblQuiz.Description

[Desolator144]

Is there any reason for "TOP (100) PERCENT"?

Nope :P but it feels the need to add them in whenever I execute the query or use the check the SQL syntax button. Is there a way to turn off that "feature" in VS 05? And your distinct worked but boy is that a far cry from real SQL. I go by the older = correcter policy when it comes to SQL and in DB2 on the AS400 it's distinct with ()'s. It should have at least worked that way but noooo. As for that group by, what if I have:
user1 test1
user1 test2
user2 test1

if the Description field (it's kinda the 'name of the quiz' field) is included in the group by, wouldn't it smush together the two test1's?

[si_the_geek]

I have no idea about VS 05, as I dont have it (and I always write queries in the DBMS itself, eg: Query Analyser for SQL Server).

As for Distinct... as far as I am aware that is the way proper SQL has always worked when multiple fields are in the Select list. I dont understand what logic DB2/AS400 would apply with the situation, presumably they were effectively converting it into some sort of Group By - this is something I dont think I have ever seen before (using about 15 different DBMS's).

A Group By on all fields will be the same as a Distinct on all fields - so all three example rows you showed would be returned. Both of the queries I posted above will give the same results.

[szlamany]

You are confused about GROUP BY and DISTINCT.

They work the same in all versions of SQL - that syntax is ages old.

Every column that is not in an AGGREGATE function or sub-query must be in the GROUP BY list.

That's the whole point.

GROUP BY collapses a recordset on common columns removing rows that are duplicated. The typical reason for this is to count those rows or add up some money in those rows.

btw - the desire to add TOP 100 PERCENT to SELECT's is because that's required to put an ORDER BY in a VIEW. A VIEW can only have an ORDER BY if the TOP clause is specified. So these silly SQL syntax generators seem to like to pop a TOP clause in queries.

[disruptivehair]

It seems that SQL Server that comes with VS 05 has never heard of DISTINCT clause. Normally it's DISTINCT(columnname) but it has no idea what that means. And the other function that would get rid of duplicates, GROUP BY doesn't work either. I put it in and it just says error by the word group. I wrote the whole statement a little different and it says "Column dbo.tblResults.UserID is invalid in the select list because it is not contained in either an aggregate function or the group by clause" and of course it says that when I put it in the group by clause and it's also in the select clause too so that's a stupid error message. Here's the two statements that don't work for some reason. If anyone knows why, that'd be great:

SELECT TOP (100) PERCENT dbo.tblResults.UserID, dbo.tblUser.UserName, dbo.tblQuiz.Description
FROM dbo.tblResults INNER JOIN
dbo.tblUser ON dbo.tblResults.UserID = dbo.tblUser.UserID INNER JOIN
dbo.tblQuiz ON dbo.tblResults.QuizID = dbo.tblQuiz.QuizID
WHERE (dbo.tblUser.IsInstructor = 'false')
GROUP BY dbo.tblResults.UserID
ORDER BY dbo.tblResults.UserID, dbo.tblQuiz.Description



SELECT TOP (100) PERCENT DISTINCT(dbo.tblResults.UserID), dbo.tblUser.UserName, dbo.tblQuiz.Description
FROM dbo.tblResults INNER JOIN
dbo.tblUser ON dbo.tblResults.UserID = dbo.tblUser.UserID INNER JOIN
dbo.tblQuiz ON dbo.tblResults.QuizID = dbo.tblQuiz.QuizID
WHERE (dbo.tblUser.IsInstructor = 'false')
ORDER BY dbo.tblResults.UserID, dbo.tblQuiz.Description

Every DBMS has a slightly different interpretation of SQL. The language SQL Server uses is called T-SQL and it's a bit different from Oracle's flavor, MySQL's flavor, PostgreSQL's flavor, etc.

If you're struggling with T-SQL there are many excellent tutorials online or available at good bookstores. When I switched from SQL Server to Oracle I was really confused, and even more so when I switched back to SQL Server; so I got some reference books. Just study up on the specific syntax of T-SQL and you should be fine.