Can you give me Alternative to Group By sql server 2008

I have a situation where I have a Select with 6 fields but I only want to group by 3 fields. As you know this looks n开发者_开发问答ot possible. What do you do in these situations?

Thanks for any feedback

It looks like you have a misunderstanding of how group by works. Look at this example:

SELECT
    SalesOrderID,
    SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail sod
GROUP BY SalesOrderID

All included columns must be either aggregated (as with LineTotal above - SUM) or grouped (as with SalesOrderID above). This will give a separate LineTotal sum for each SalesOrderID.

Group By doesn't make sense without any aggregates.

But what do you want to do with the 3 fields that are not grouped? You need to apply an aggregate function on each of them to make it a correct statement.

Something like this:

SELECT
  Col1, Col2, Col3
, MAX(Col4) AS Col4
, MAX(Col5) AS Col5
, MAX(Col6) AS Col6
FROM Table1
GROUP BY 
  Col1, Col2, Col3

Or this, if you want the values for the non-grouped columns to come from the same row:

SELECT * FROM (
  SELECT *, _SeqNo = ROW_NUMBER() OVER (
    PARTITION BY Col1, Col2, Col3 
    ORDER BY (SELECT 1))
  FROM Table1) a
WHERE _SeqNo = 1

I wanted to use GROUP BY in a similar manner but then I realized that it was unnecessary. I didn't need to perform any aggregation, and it sounds like you don't want to either. The alternative was just to use ORDER BY on multiple columns. Try modifying your query to something like this:

SELECT COL1, COL2, COL3, COL4, COL5, COL6
FROM TABLE1
ORDER BY COL1, COL2, COL3;