CODEWITHSUNDEEP
mysqlsqlsql-order-by
2787184
2787184

Reputation: 3901

Order by clause is not working with this query

CREATE TABLE `cc_comment_master` (
  `ID` int(11) DEFAULT NULL,
  `DRAFT_SALESDOC_HEADER_ID` int(11) DEFAULT NULL,
  `PRODUCT_ID` int(11) DEFAULT NULL,
  `PRODUCT_NUMBER` varchar(50) DEFAULT NULL,
  `COMMENT_DESC` varchar(100) DEFAULT NULL,
  `COMMENT_BY` varchar(50) DEFAULT NULL,
  `COMMENT_ON` datetime DEFAULT NULL,
  `MODIFIED_BY` varchar(100) DEFAULT NULL,
  `MODIFIED_ON` datetime DEFAULT NULL,
  `CREATED_BY` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert  into `cc_comment_master`(`ID`,`DRAFT_SALESDOC_HEADER_ID`,`PRODUCT_ID`,`PRODUCT_NUMBER`,`COMMENT_DESC`,`COMMENT_BY`,`COMMENT_ON`,`MODIFIED_BY`,`MODIFIED_ON`,`CREATED_BY`) 
values (1,1,1,'abc','first','test','2016-01-12 10:43:48','test',NULL,'test'),(2,2,2,'def','second','test','2016-01-12 10:43:53','test',NULL,'test'),(3,3,3,'xyz','third','test','2016-01-12 10:43:56','test',NULL,'test'),(4,4,4,'pqr','four','test','2016-01-12 10:44:33','test',NULL,'test');

Query:

declare @comments  varchar(max)
set @comments = ''

Select 
    @comments = @comments +''+ COMMENT_DESC+'-COMMENT_SEPERATOR-' 
from 
    CC_COMMENT_MASTER c 
where 
    c.DRAFT_SALESDOC_HEADER_ID = 1
    and PRODUCT_NUMBER = 'abc'
order by 
    c.COMMENT_ON  desc   --order by not working 

select @comments;

I want to select all COMMENT_DESC appended in one string, in descending order by COMMENT_ON.

Without order by clause I am getting all COMMENT_DESC in asc order in one string. when I tried to order by COMMENT_ON desc it is not working.

Output:

first-COMMENT_SEPERATOR-

Expected output:

first-COMMENT_SEPERATOR-second-COMMENT_SEPERATOR-third--COMMENT_SEPERATOR-

Error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

Upvotes: 0

Views: 278

Answers (3)

2787184
2787184

Reputation: 3901

Following query is working for me.

 DECLARE @comments nvarchar(max);
 SET @comments=''; 

 SELECT @comments= @comments +''+ TEMP.COMMENT_DESC +'COMMENT_SEPERATOR' 
 FROM
 (SELECT *, ROW_NUMBER() OVER (ORDER BY c.COMMENT_ON) AS RowNum FROM CC_COMMENT_MASTER   c  
 WHERE c.DRAFT_SALESDOC_HEADER_ID=1 AND c.PRODUCT_NUMBER='abc' ) AS TEMP
 ORDER BY TEMP.RowNum;

  SELECT @comments;

Upvotes: 0

Narasimha Maiya
Narasimha Maiya

Reputation: 1029

Their will be no multiple record to order for that condition in the where clause. Just remove the condition, I think it will work

declare @comments varchar(max) set @comments = '' Select @comments = @comments +''+ COMMENT_DESC+'-COMMENT_SEPERATOR-' from CC_COMMENT_MASTER c order by c.COMMENT_ON desc

And remove that desc it will give output in backwards.

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

declare @comments  varchar(max)
set @comments = ''

Select 
    @comments =  @comments +''+ COMMENT_DESC+'-COMMENT_SEPERATOR-' 
from 
    (select top 100 percent * from CC_COMMENT_MASTER c  order by COMMENT_ON  desc) as t 

select @comments;

Upvotes: 0

Related Questions