how to use the MAX function in SQL server?

[Desolator144]

I want the entire row with those 4 fields returned with the highest attempt number for this statement:

SELECT DISTINCT UserID, QuizID, AttemptNum, DateSaved
FROM tblResults
WHERE (QuizID = 5) AND (UserID = 4)

couldda swarn in real SQL (aka not microsoft's stupid SQL) you can just wrap the AttemptNum in a MAX() clause in the select clause but I guess not. What's the way to do it in their stupid language if they even left a way to do it at all.

[si_the_geek]

"microsoft's stupid SQL" as you call it, is real SQL - it follows the standards of SQL 92, whereas whatever system(s) you used before appear to have used their own standards.

In order to return a calculated value (eg: Max or Count) as part of your query results, you use a Group By like this:

SELECT UserID, QuizID, Max(AttemptNum), DateSaved
FROM tblResults
WHERE (QuizID = 5) AND (UserID = 4)
GROUP BY UserID, QuizID, DateSaved

..note that the Max wil only apply to the rows that have the same values of UserID, QuizID, and DateSaved.

An alternative (which is not supported by all DBMS's) is to put a sub-query directly into the Select list like this:

SELECT DISTINCT UserID, QuizID, (SELECT Max(AttemptNum) FROM tblResults), DateSaved
FROM tblResults
WHERE (QuizID = 5) AND (UserID = 4)

[Desolator144]

the version of SQL I use is the one in my SQL book that claims it's universal to all DMBS's and is a pretty recent printing. We practice it on OS400 on an IBM iSeries which btw were invented in 1980 and haven't changed much since. I used the nested query version you got there but I shouldn't have had to. If I had data like this:
UserID QuizID, AttemptNum, DateSaved
1 | 3 | 1 | 10/15/2006
2 | 3 | 2 | 10/15/2006

which UserID would appear on the returned row with your triple group by statement? The date saved and the quiz ID would try to group because they're similar but what would happen to the User ID? If it left them both there then I'd have two rows so that's the same as not using group by at all. If it just took the first one that'd be a really inaccurate way to do it. If it took the highest (or lowest) value that's not always the best way depending on the circumstances. If it left it null or removed that column that'd be useless. If it just randomly picked, that'd be stupid. I don't see how doing group by on all columns could do anything at all. So far I've used 3 DBMS's and 2 and possibly the third one of them all use group by on single columns and distinct with ()'s.

[jmcilhinney]

There's really no such thing as "universal" SQL. There is an SQL standard but no database adhere's to the standard in all situations. Having said that, "SQL in a Nutshell" provides the SQL2003 standard and it also provides information on each platforms individual variations. My copy of "SQL in a Nutshell" clearly states

Include all non-aggregate columns in the GROUP BY clause.

That's pretty clear. Any database that doesn't follow that rule is not following the SQL2003 standard. Does that make them the stupid ones? Also, MSDN has a full T-SQL reference so you can go and see their stupid implementation for yourself. No database worth its salt follows the SQL standard completely because they have all added variations and additions that they consider make their implementation easier or better. That's ALL databases. If things don't work the way you expect don't assume that your tools are stupid. Assume that you're making invalid assumptions.

[RobDog888]

SQL Server uses ANSI 92 structured query language as its standard. Other database manufacturers use ANSI 92 also as well as some unique one off "standards". ANSI has a few standards that the database manufacturers can decide to use or not. Its up to them. Even Access uses ANSI 89 standards by default.

[Desolator144]

I dunno about that cuz adding columns to a group by clause that you specifically don't want to be grouped together when duplicated values are found is pretty much the definition of stupid. A LOT of things about most BDMS's are stupid. Running DISTINCTS on primary key fields instead of filtering those clauses out, preset resource wasting field sizes, changing SQL in general, useless error messages, wasted space on deleted data, the need for commit statements for stupid programmers that don't verify data before sending it, the manualness and disc choosing on mainframes, and the fact that anyone still uses DB2/AS400s at all. I hate databases, I think we as programmers should boycott them. Take a look at winsite.com or download.com or even just AIM Triton and tell me we don't need more quality software engineers working on writing utilities instead.

[szlamany]

I want the entire row with those 4 fields returned with the highest attempt number for this statement:

Start thinking in set-based logic and your request is quite simple.

Select UserID, QuizId, AttemptNum, DateSave
   From tblResults TR1
   Where QuizId=5 and UserId=4
         and AttemptNum=(Select Max(TR2.AttemptNum) From tblResults TR2
                           Where TR2.UserId=TR1.UserId and TR2.QuizId=TR1.QuizId)

GROUP BY and DISTINCT will never serve the need you asked about. You are looking for a particular row - not looking to aggregate anything in the working resultset.

btw - MS SQL SERVER is not a MS product at all. They purchased it from SYBASE - makers of SQL products that ran on mainframes from probably 1950!

If you keep bashing SQL in your questions here then some of us might stop wanting to answer your questions

It's all good - and it's all understandable - and we all are willing to help.