inlcude in order by but not groupby [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

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