Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Order BY Clause Issue

    Code:
    CREATE TABLE EMP (name nvarchar(10),ename nvarchar(2),txn float,[2000] float,
    [2001] float,[2002] float,[2003] float,[2004] float,[2005] float)
    
    insert into EMP values('Lakshmi','CC',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('CLakshmi','BB',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Laknar','AA',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Narayan','DD',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('karthik','EE',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Ckarthik','CC',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Narayanan','CC',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Lakshmi','CC',2,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Lakshmi','CC',3,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Narayan','DD',2,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Narayan','DD',3,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Tiger','DD',1,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Tiger','DD',2,2000,3000,4000,5000,6000,7000)
    insert into EMP values('Tiger','DD',3,2000,3000,4000,5000,6000,7000)
    
    select * from dbo.emp
    
    with test as (select name,ename,[Sales],SUM(case when txn=1 then Amt else 0 end) as [1],sum(case when txn=2 then Amt else 0 end)as [2],
    sum(case when txn=3 then Amt else 0 end)as [3] from dbo.emp 
    unpivot(Amt for [Sales] in ([2000],[2001],[2002],[2003],[2004],[2005])) 
    as Amt group by name,ename,[Sales] ) select * from test order by name,sales
    the above code is working perfectly in 2008 R2 version.

    but in SQL Server 2005 standrad edition sorting is not happening properly.
    im trying to sort using name and sales column
    it sorts Even numbers separately and Odd number separately for the sales column.

    Please suggest me. is this a bug in 2005 standard edition.

  2. #2
    Join Date
    Jul 2008
    Posts
    94
    SELECT * FROM(select name,ename,[Sales],SUM(case when txn=1 then Amt else 0 end) as [1],sum(case when txn=2 then Amt else 0 end)as [2],
    sum(case when txn=3 then Amt else 0 end)as [3],ROW_NUMBER() OVER (PARTITION BY name order by name,Sales) as ROWNUM from dbo.emp
    unpivot(Amt for [Sales] in ([2000],[2001],[2002],[2003],[2004],[2005]))
    as Amt group by name,ename,[Sales]) AS RNM ORDER BY name,ROWNUM


    im trying to sort the above query but sorting is problem.
    even i order by rownum column. i still get below result

    Name ename Sales
    CKarthik CC 2
    CLakshmi BB 1
    Laknar AA 2
    .....so on

    Please suggest me.

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    if i use delete statement im not able to sort the records properly. only i can able to sort with truncate and insert.


    i must use delete statement and load the load the table with correct sort order.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I'm not sure if this will help you, but I found this:
    When PIVOT and UNPIVOT are used against databases that are upgraded to SQL Server 2005 or later, the compatibility level of the database must be set to 90 or higher.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have run your code on our SQL Server 2005 database and the result set is ordered as it should be.
    Code:
    Ckarthik	CC	2000	2000	0	0
    Ckarthik	CC	2001	3000	0	0
    Ckarthik	CC	2002	4000	0	0
    Ckarthik	CC	2003	5000	0	0
    Ckarthik	CC	2004	6000	0	0
    Ckarthik	CC	2005	7000	0	0
    CLakshmi	BB	2000	2000	0	0
    CLakshmi	BB	2001	3000	0	0
    CLakshmi	BB	2002	4000	0	0
    CLakshmi	BB	2003	5000	0	0
    CLakshmi	BB	2004	6000	0	0
    CLakshmi	BB	2005	7000	0	0
    karthik	EE	2000	2000	0	0
    karthik	EE	2001	3000	0	0
    karthik	EE	2002	4000	0	0
    karthik	EE	2003	5000	0	0
    karthik	EE	2004	6000	0	0
    karthik	EE	2005	7000	0	0
    Laknar	AA	2000	2000	0	0
    Laknar	AA	2001	3000	0	0
    Laknar	AA	2002	4000	0	0
    Laknar	AA	2003	5000	0	0
    Laknar	AA	2004	6000	0	0
    Laknar	AA	2005	7000	0	0
    Lakshmi	CC	2000	2000	2000	2000
    Lakshmi	CC	2001	3000	3000	3000
    Lakshmi	CC	2002	4000	4000	4000
    Lakshmi	CC	2003	5000	5000	5000
    Lakshmi	CC	2004	6000	6000	6000
    Lakshmi	CC	2005	7000	7000	7000
    Narayan	DD	2000	2000	2000	2000
    Narayan	DD	2001	3000	3000	3000
    Narayan	DD	2002	4000	4000	4000
    Narayan	DD	2003	5000	5000	5000
    Narayan	DD	2004	6000	6000	6000
    Narayan	DD	2005	7000	7000	7000
    Narayanan	CC	2000	2000	0	0
    Narayanan	CC	2001	3000	0	0
    Narayanan	CC	2002	4000	0	0
    Narayanan	CC	2003	5000	0	0
    Narayanan	CC	2004	6000	0	0
    Narayanan	CC	2005	7000	0	0
    Tiger	DD	2000	2000	2000	2000
    Tiger	DD	2001	3000	3000	3000
    Tiger	DD	2002	4000	4000	4000
    Tiger	DD	2003	5000	5000	5000
    Tiger	DD	2004	6000	6000	6000
    Tiger	DD	2005	7000	7000	7000
    Can you check the Collation ?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •