CODEWITHSUNDEEP
sql-server
Malay Dave
Malay Dave

Reputation: 115

How can i use Order by in View of SQL?

I have this View and i want to get order by the closeindays in sequence. So, How can i order by the closeindays?? 

Create view XYZ_View as     select      
    ROW_NUMBER() over(order by RecordNoID) as RecordNoNo,
    ROW_NUMBER() over(order by RecordNo) as RecordNum,
    XYZ_View .RecordNoID as RecordNoId,
    XYZ_View .RecordNo as RecordNo,
    XYZ_View .cActiveFlag,
    XYZ_View .nCreatedUserNo,
    XYZ_View .dCreatedOn,
    XYZ_View .nUserNo,
    XYZ_View .dModifyOn,
    XYZ_View .cReplicaFlag,
    XYZ_View .[Status],
    XYZ_View .CheckOutBy,
    XYZ_View .nVersionNo,
    XYZ_View .vVersionRemarks,
    XYZ_View .nDocID,
    XYZ_View .vSendRemarks,
    XYZ_View .nObjectVersionNo,
    XYZ_View .nRTID,
    XYZ_View .LocationName,
    XYZ_View .GroupName,
    XYZ_View .XYZ_Due_Date,
    XYZ_View .XYZ_Closure_Date,
    case
        when XYZ_View .[Status] like '%Lock%'
                then 'Close'
        else
                'Open'
    end  as Final_Status,

        case
        when XYZ_View .[Status] like '%Lock%'
                then
                    case
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)    >= 0
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 10
                                then '0 - 10'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 11
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 20
                                then '11 - 20'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 21
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 30
                                then '21 - 30'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 31
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 40
                                then '31 - 40'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 41
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 50
                                then '41 - 50'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 51
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 60
                                then '51 - 60'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,RXYZ_View .dModifyOn)   >= 61
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 70
                                then '61 - 70'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 71
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 80
                                then '71 - 80'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 81
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 90
                                then '81 - 90'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)   >= 91
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 100
                                then '91 - 100'
                        when DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn)  >= 101
                            and DATEDIFF(D,XYZ_View .dCreatedOn,XYZ_View .dModifyOn) <= 200
                                then '>= 101'
                    end
                else
                    '-1' end as ClosedInDays,
            case    when XYZ_View .[Status] like '%Lock%' and convert(date,dModifyOn,105)> convert(date,XYZ_Due_Date,105))
                then
                    'Yes'   end as ClosedAfterDueDate   from XYZ_View

I need to order by the closed in Days like in sequence 0-10,11-20,21-30, etc...

Thanks in advance

Upvotes: 0

Views: 80

Answers (2)

Eralper
Eralper

Reputation: 6622

By default views and tables are not stored in an order.

You can order the view outputs in the SELECT statement like,

select * from XYZ_View order by ClosedInDays

Although you can create a view with Order By clause as follows

create view v_tables
as

select top 100 percent * from sys.tables order by name

go

It will not work as expected

Upvotes: 1

Vishal Khunt
Vishal Khunt

Reputation: 170

View is not allow to "order by". you can use procedure instead of view.

Upvotes: 0

Related Questions