Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group by without soring Expand / Collapse
Author
Message
Posted Saturday, September 14, 2013 6:49 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
Hi, this is initializations:

CREATE TABLE #table1(col1 nvarchar(1), col2 int);

INSERT INTO #table1 VALUES('B','100');
INSERT INTO #table1 VALUES('A','200');
INSERT INTO #table1 VALUES('B','300');
INSERT INTO #table1 VALUES('C','400');

Here is my query:
WITH CTE (col1,col2) as (
SELECT col1, SUM(col2)
FROM #table1
GROUP BY col1
)

SELECT * FROM cte

Here is output:

col1,col2
----------------
A,200
B,400
C,400

Why GROUP BY statement sort my table automatically?
I want to display my table as it is.
Is it possible?

Thank you very much for help.


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1494804
Posted Saturday, September 14, 2013 7:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 6:38 AM
Points: 221, Visits: 1,106
CREATE TABLE #table1(col1 nvarchar(1), col2 int, OrderMe int);

INSERT INTO #table1 VALUES('B','100',0);
INSERT INTO #table1 VALUES('A','200',1);
INSERT INTO #table1 VALUES('B','300',0);
INSERT INTO #table1 VALUES('C','400',1);


WITH CTE (col1,col2, OrderMe) as (
SELECT col1, SUM(col2), OrderMe
FROM #table1
GROUP BY col1, OrderMe
)

SELECT col1,Col2 FROM cte
ORDER BY OrderMe

Group by isnt' an ordering function, and if you want a specific order on your table, its best to add an ordering key of some kind.
Post #1494806
Posted Saturday, September 14, 2013 9:12 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
Thank you for reply.
My table have key, problem is if I sort my table with key I have to use it in "GROUP BY" and it generate incorrect output.

See this:
CREATE TABLE #table1(ID INT ,col1 nvarchar(1), col2 int);

INSERT INTO #table1 VALUES(1,'B','100');
INSERT INTO #table1 VALUES(2,'A','200');
INSERT INTO #table1 VALUES(3,'B','300');
INSERT INTO #table1 VALUES(4,'C','400');


WITH CTE (ID,col1,col2) as (
SELECT ID ,col1, SUM(col2)
FROM #table1
GROUP BY col1, ID
)

SELECT col1,Col2 FROM cte
ORDER BY ID

Col1,Col2
---------------
B,100
A,200
B,300
C,400

As you see KEY didn’t allowed me to group my data by Col1


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1494818
Posted Saturday, September 14, 2013 12:44 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 06, 2014 6:34 AM
Points: 109, Visits: 256
Try MIN(ID) in the inner select and it will work i think?
Post #1494836
Posted Saturday, September 14, 2013 11:09 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 21, 2014 1:28 AM
Points: 59, Visits: 269
siggemannen (9/14/2013)
Try MIN(ID) in the inner select and it will work i think?


haha yes
Thanks it worked, much appreciated


___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
Post #1494858
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse