Sybase -> MSSQL "group by" return order

Posted on 2006-04-20
Medium Priority
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
  • 2
LVL 75

Accepted Solution

Anthony Perkins earned 1000 total points
ID: 16504501
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
ID: 16504512
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

831 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