inlcude in order by but not groupby [Resolved]

Posted by Oswaldlily under Sql Server on 3/7/2014 | Points: 10 | Views : 691 | Status : [Member] | Replies : 2
need to inlcude one particular column in order by

but dont want to include in group by

is it possible?




Responses

Posted by: Bandi on: 3/7/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
You can NOT put non-Group by columns in the ORDER BY clause...

For that scenario you can make use of OVER() clause.

-- Just run below script
declare @Table table(magno varchar(10), price int, date  DaTE)
insert @Table
SELECT '13445A', 45, '20/09/2013' union all
SELECT '13445B', 35, '20/09/2013' union all
SELECT '13445C', 10, '20/09/2013'

--output shd be
--134450 90(sum) 20/09/2013

SELECT distinct LEFT(magno, LEN(magno)-1)+ '0' as MagNo, date, SUM(price) OVER(PARTITION BY LEFT(magno, LEN(magno)-1)+ '0', date) as MagNum
FROM @Table
ORDER BY magno


NOTE: If the above is not working for you, post us back the exact requirement

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: A2H on: 3/7/2014 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi,
I guess you want to Order by a particular column but that particular column should not a part Group by clause. if that is the case all you need to do is to run some aggregate function(MAX) over non-grouping columns.

Please find a working sample Given below

Create Table and Populate Dummy Values


-- Creating Sample Table
CREATE TABLE YourTableName
(

Dept varchar(24),
Name varchar(24),
Subject varchar(24)
);
--Populate some dummy data
INSERT INTO YourTableName
(Dept, Name, Subject)
VALUES
('Value1','Test1','C'),
('Value2','Test2','C++'),
('Value1','Test3','DSP'),
('Value4','Test4 ','Aerodynamics'),
('Value2','Test5 ','Electrical'),
('Value1','Test6','C++'),
('Value4','Test7','Astrophysics')


Select query is given below

SELECT Max(Dept) as Department,Name,Subject FROM YourTableName

Group by Name,Subject ORDER BY MAX(Dept)


You can also find a working sample of above code in below link
http://sqlfiddle.com/#!3/ef63f/1

Thanks,
A2H
My Blog

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response