CODEWITHSUNDEEP
sqlsql-serverselect
user2082503
user2082503

Reputation: 109

Select Distinct Error

I am having this problem with my database. My code is:

 CREATE PROCEDURE [dbo].[test1]
    @den nchar(14) = ''         
 AS
 BEGIN  
   SET NOCOUNT ON;

   DECLARE @SQL AS varchar(3000) = ''   
   DECLARE @WHERE1 AS varchar(1000) = ''    
   DECLARE @ORDERBY1 AS varchar(1000) = ''

   SET @WHERE1 = @WHERE1 + ' WHERE [dbo].table2.sag=1 '

   IF (@den != '')      
      SET @WHERE1 = @WHERE1 + ' AND RIGHT(''0000000000''+CONVERT(VARCHAR(10),[dbo].table1_den_id1),10) +RIGHT(''0000''+CONVERT(VARCHAR(4),[dbo].table1_den_id2),4) = ''' + RIGHT('00000000000000'+RTRIM(@den),14) + ''''

   SET @ORDERBY1 = @ORDERBY1 + 'GROUP BY [dbo].table2.item_barcord      
ORDER BY LEFT([dbo].table2.item_barcord,13) ASC'

   SET @SQL = @SQL + '      
          SELECT            
          DISTINCT          
          ''JAN'' + LEFT([dbo].table2.item_barcord,13) AS ''code'',             
          LEFT(MIN([dbo].table2.item_name),15) AS ''name''      
          FROM [dbo].table1         
          INNER JOIN [dbo].table2 ON [dbo].table1.id = [dbo].table2.id          '           + @WHERE1       
          + @ORDERBY1

   DECLARE @RET AS numeric(3,0) = 0

   EXEC(@SQL)   
   SET @RET = @@ROWCOUNT

   RETURN @RET
END

However I am getting this error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Please help me.

Upvotes: 0

Views: 251

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270793

I think you should change the query so you don't need a select distinct:

      SELECT ''JAN'' + LEFT([dbo].table2.item_barcord,13) AS ''code'',  
      . . . 
      group by LEFT([dbo].table2.item_barcord,13)

Then the order by will work right. As an added bonus, the query will make more sense -- select distinct in an aggregation is a bit difficult to figure out.

Upvotes: 1

Iswanto San
Iswanto San

Reputation: 18569

Try to change this line :

SET @ORDERBY1 = @ORDERBY1 + 'GROUP BY [dbo].table2.item_barcord     
ORDER BY LEFT([dbo].table2.item_barcord,13) ASC'

To :

SET @ORDERBY1 = @ORDERBY1 + 'GROUP BY [dbo].table2.item_barcord     
ORDER BY LEFT([dbo].table2.item_barcord,13), 2 ASC'

More: SELECT DISTINCT and ORDER BY

Upvotes: 1

Related Questions