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..