Order by field not in Group by

Hi all,
I'm stuck here with what I thought would be a pretty simple query. In fact, it works fine in MySQL, but gives me trouble in MS SQL..
Suppose, we have 2 tables (those are MySQL versions):

CREATE TABLE T1
(ID INT NOT NULL AUTO_INCREMENT ,
NAME VARCHAR(50),
PRIMARY KEY (ID) )

CREATE TABLE T2
(ID INT NOT NULL AUTO_INCREMENT,
Message VARCHAR(50),
DateAdded datetime,
T1_ID INT,
PRIMARY KEY (ID),
FOREIGN KEY (T1_ID) REFERENCES T1(ID) )

with some data

INSERT INTO T1 (NAME) VALUES ('BILL')
INSERT INTO T2 (Message, DateAdded, T1_ID) VALUES ('Hi', Now(), 1)
INSERT INTO T2 (Message, DateAdded, T1_ID) VALUES ('Bye', Now(), 1)

Now, I want to select names from T1 along with latest message. In MySQL, following works fine:

SELECT T1.`NAME` FROM T1, T2
WHERE T2.T1_ID = T1.ID
GROUP BY T1.ID
ORDER BY T2.DateAdded DESC

in MS SQL it gives me
Column "T2.DateAdded " is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Duh.. If I'll include this column in Group by I won't get the results I want.

I also tried
SELECT T1_ID FROM (SELECT T1_ID FROM T2 ORDER BY DateAdded DESC) AS A
GROUP BY T1_ID

which again worked fine in MySQL, but gave "The ORDER BY clause is invalid in ... subqueries" error.
This should be a pretty simple query, yet I'm stuck..

You need to include it in SELECT list.

SELECT T1.`NAME`, T2.DateAdded
FROM T1, T2
WHERE T2.T1_ID = T1.ID
GROUP BY T1.ID
ORDER BY T2.DateAdded DESC

Quote:

---

You need to include it in SELECT list.

---

That would give me same " invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause" error.

Column name has to be in group by clause or aggregate function.

I think you pasted the wrong error message and I got confused. I recreated the tables and data in my environment and ran your original query, I got this

Column 'T1.NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which is normal because you can't have a column in SELECT list if it is not part of GROUP BY or if you have not applied aggregate function on it. So your original query

SELECT T1.NAME FROM T1, T2
WHERE T2.T1_ID = T1.ID
GROUP BY T1.ID
ORDER BY T2.DateAdded DESC

would also fail if you do not have ORDER BY in there.

should be rewritten as

SELECT max(T1.NAME) FROM T1, T2
WHERE T2.T1_ID = T1.ID
GROUP BY T1.ID

Now to order by dateadd, you need to add dateadd in SELECT list, again you need to put an aggregate around it to work as

SELECT max(T1.NAME),max(T2.DateAdded) FROM T1, T2
WHERE T2.T1_ID = T1.ID
GROUP BY T1.ID
ORDER BY max(T2.DateAdded) DESC

Can you post what data you want as the result.

Got it:)
I thought there must be a fairly simple way to do it.
I still don't get why I can't use ORDER BY in subqueries, though..
Thanks for replies !:)

The error for

SELECT T1_ID FROM (SELECT T1_ID FROM T2 ORDER BY DateAdded DESC) AS A
GROUP BY T1_ID

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Is ANSI requirement, the inline view can't have order by, but workaround is to use TOP

SELECT T1_ID FROM (SELECT TOP 100 PERCENT T1_ID FROM T2 ORDER BY DateAdded DESC) AS A
GROUP BY T1_ID

Now the query is not ANSI compliant, but it works.

Yep, that's what I did.
Don't see a logic in a requirement, though. And it can be bypassed so easily..

what about this?...I need to return a scalar value that is the count of the number of scripts with an id > x

SELECT COUNT(tblScripts.ID) AS IDCount FROM tblScripts WHERE tblScripts.ID > 0 ORDER BY tblScripts.ID ASC

Never mind LOL, wasnt thinking on that one.


SELECT COUNT(tblScripts.ID) AS IDCount FROM tblScripts WHERE tblScripts.ID > 0

it is actually simple, all you need add T2.DateAdded to the group by clause

SELECT T1.NAME FROM T1, T2
WHERE T2.T1_ID = T1.ID
GROUP BY T1.ID,T2.DateAdded
ORDER BY T2.DateAdded DESC

Sorry never mind,
didn't read entire discussion