September 14, 2013 at 6:49 am
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.
September 14, 2013 at 7:11 am
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.
September 14, 2013 at 9:12 am
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.
September 14, 2013 at 12:44 pm
Try MIN(ID) in the inner select and it will work i think?
September 14, 2013 at 11:09 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply