Sybase -> MSSQL "group by" return order

Posted on 2006-04-20
Last Modified: 2008-01-09
I'm working on converting some queries from Sybase to Microsoft SQL Server. One of the things I need to correct is the "group by" usage. Sybase allows you to put non-aggregate items in the select clause that don't appear in the group by clause. MSSQL requires every non-aggregate to appear in the group by clause.

I can just add every missing item from the select into the group by, but I'm concerned about the return order. Will the order that the matches are returned change if I add the additional group by items? There may be code that's depending on the results to be returned in a particular order (bad, I know, but I have no control over that). If adding new group by items will skew the order, is there a quick & easy way to do it right?

Since I'm looking for general information on how group by results are ordered in Sybase versus MSSQL,  I haven't posted a specific query's code. I need enough general information to go on and convert all these queries myself.
Question by:jsprad
    LVL 75

    Accepted Solution

    Don't change the GROUP BY items, it will skew you results, instead use an aggregate functions such as MAX or MIN, as in:

    Select Col1, Col2, MIN(Col3) Col3
    From  YourTableName
    Group By Col1, Col2
    LVL 75

    Expert Comment

    by:Anthony Perkins
    If on the other hand you want to return all the values of Col3 even though Col1 and Col2 are the same, than you will have to use a self join against the same table.  But without a query or even some sample data, it is hard to be specific.  In the example I posted previously it would be something like this:
    Select Col1, Col2, Col3
    From YourTableName t
             Inner Join (Select Col1, Col2, MIN(Col3) Col3
                              From  YourTableName
                              Group By Col1, Col2) d On t.Col1 = d.Col1 And t.Col2 = d.Col2

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now