# Database Discussions > Microsoft SQL Server 2005 >  Order by field not in Group by

## elt

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

----------


## skhanal

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

----------


## elt

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

----------


## rmiao

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

----------


## skhanal

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.

----------


## elt

Got it :Smilie: 
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 ! :Smilie:

----------


## skhanal

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.

----------


## elt

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

----------


## Polymorpher

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

----------


## shamshe

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

----------


## shamshe

Sorry never mind,
didn't read entire discussion

----------

