• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Sybase -> MSSQL "group by" return order

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.
0
jsprad
Asked:
jsprad
  • 2
1 Solution
 
Anthony PerkinsCommented:
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
0
 
Anthony PerkinsCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now