Results 1 to 11 of 11

Thread: Order by field not in Group by

  1. #1
    Join Date
    Mar 2007
    Posts
    4

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

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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

  3. #3
    Join Date
    Mar 2007
    Posts
    4
    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.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Column name has to be in group by clause or aggregate function.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  6. #6
    Join Date
    Mar 2007
    Posts
    4
    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 !

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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.

  8. #8
    Join Date
    Mar 2007
    Posts
    4
    Yep, that's what I did.
    Don't see a logic in a requirement, though. And it can be bypassed so easily..

  9. #9
    Join Date
    Oct 2007
    Posts
    1
    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.

  10. #10
    Join Date
    Jun 2007
    Posts
    41
    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

  11. #11
    Join Date
    Jun 2007
    Posts
    41
    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
  •