-
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
-
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
Last edited by Polymorpher; 10-18-2007 at 12:57 PM.
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|