Solved

ORDER BY Date not working in Views

Posted on 2009-04-02
7
757 Views
Last Modified: 2012-06-27
Hello,

I am having a situation with the View treating the SQL statement below, when I run this statement, it orders all the records by date (oldest--newest), however when I make the View
with the same statement and run it,  its ordering it rather by Product type name.
I am unable to figure this out.

I want it to show the results order by date.

Please can someone advise?

Thanks for any help.

Regards
M
0
Comment
Question by:meinIT
7 Comments
 

Author Comment

by:meinIT
ID: 24047839
please have the SQL and the view attached..

thanks
m
SQL Statement

=============
 

SELECT     TOP (100) PERCENT GCT.GiftCertificateTypeName, DATEPART(day, GCH.GiftHistDateAdded) AS day, DATEPART(month, GCH.GiftHistDateAdded) 

                      AS month, DATEPART(year, GCH.GiftHistDateAdded) AS year, COUNT(GCH.GiftCodeID) AS NumCertsSent, COALESCE (PT.PonyName, DN.DonkeyName, 

                      MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName) AS SideName

FROM         dbo.GiftCertificateHistory AS GCH INNER JOIN

                      dbo.GiftCertificate AS GC ON GCH.GiftCodeID = GC.GiftCodeID INNER JOIN

                      dbo.GiftCertificateType AS GCT ON GC.GiftCertificateTypeID = GCT.GiftCertificateTypeID LEFT OUTER JOIN

                      dbo.AdoptAPony AS AP ON GC.GiftCodeID = AP.GiftCodeID LEFT OUTER JOIN

                      dbo.PonyType AS PT ON PT.PonyID = AP.PonyID LEFT OUTER JOIN

                      dbo.AdoptADonkey AS AD ON GC.GiftCodeID = AD.GiftCodeID LEFT OUTER JOIN

                      dbo.DonkeyName AS DN ON DN.DonkeyID = AD.DonkeyID LEFT OUTER JOIN

                      dbo.AdoptAMonkey AS AM ON GC.GiftCodeID = AM.GiftCodeID LEFT OUTER JOIN

                      dbo.MonkeyName AS MN ON MN.MonkeyID = AM.MonkeyID LEFT OUTER JOIN

                      dbo.AdoptABigCat AS ABC ON GC.GiftCodeID = ABC.GiftCodeID LEFT OUTER JOIN

                      dbo.BigCatName AS BC ON BC.BigCatID = ABC.BigCatID LEFT OUTER JOIN

                      dbo.AdoptATurtle AS TA ON GC.GiftCodeID = TA.GiftCodeID LEFT OUTER JOIN

                      dbo.TurtleName AS TN ON TN.TurtleID = TA.TurtleID LEFT OUTER JOIN

                      dbo.LearnItTaken AS LT ON GC.GiftCodeID = LT.GiftCodeID LEFT OUTER JOIN

                      dbo.LearnItName AS LN ON LN.LearnItID = LT.LearnItID LEFT OUTER JOIN

                      dbo.AdoptAPenguin AS PA ON GC.GiftCodeID = PA.GiftCodeID LEFT OUTER JOIN

                      dbo.PenguinName AS PN ON PN.PenguinID = PA.PenguinID

WHERE     (GCH.StatusID = 'REGISTERED') AND (GCH.GiftHistDateAdded >= DATEADD(dd, - 7, CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))) AND

                       (GCH.GiftHistDateAdded < GETDATE() - 1)

GROUP BY GCT.GiftCertificateTypeName, COALESCE (PT.PonyName, DN.DonkeyName, MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName), 

                      DATEPART(day, GCH.GiftHistDateAdded), DATEPART(month, GCH.GiftHistDateAdded), DATEPART(year, GCH.GiftHistDateAdded)

ORDER BY DATEPART(year, GCH.GiftHistDateAdded) ,DATEPART(month, GCH.GiftHistDateAdded)  ,DATEPART(day, GCH.GiftHistDateAdded)  
 
 

View

====

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO
 

CREATE VIEW [dbo].[viewWeeklyRegistrationsReport]

AS

SELECT     TOP (100) PERCENT GCT.GiftCertificateTypeName, DATEPART(day, GCH.GiftHistDateAdded) AS day, DATEPART(month, GCH.GiftHistDateAdded) 

                      AS month, DATEPART(year, GCH.GiftHistDateAdded) AS year, COUNT(GCH.GiftCodeID) AS NumCertsSent, COALESCE (PT.PonyName, DN.DonkeyName, 

                      MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName) AS SideName

FROM         dbo.GiftCertificateHistory AS GCH INNER JOIN

                      dbo.GiftCertificate AS GC ON GCH.GiftCodeID = GC.GiftCodeID INNER JOIN

                      dbo.GiftCertificateType AS GCT ON GC.GiftCertificateTypeID = GCT.GiftCertificateTypeID LEFT OUTER JOIN

                      dbo.AdoptAPony AS AP ON GC.GiftCodeID = AP.GiftCodeID LEFT OUTER JOIN

                      dbo.PonyType AS PT ON PT.PonyID = AP.PonyID LEFT OUTER JOIN

                      dbo.AdoptADonkey AS AD ON GC.GiftCodeID = AD.GiftCodeID LEFT OUTER JOIN

                      dbo.DonkeyName AS DN ON DN.DonkeyID = AD.DonkeyID LEFT OUTER JOIN

                      dbo.AdoptAMonkey AS AM ON GC.GiftCodeID = AM.GiftCodeID LEFT OUTER JOIN

                      dbo.MonkeyName AS MN ON MN.MonkeyID = AM.MonkeyID LEFT OUTER JOIN

                      dbo.AdoptABigCat AS ABC ON GC.GiftCodeID = ABC.GiftCodeID LEFT OUTER JOIN

                      dbo.BigCatName AS BC ON BC.BigCatID = ABC.BigCatID LEFT OUTER JOIN

                      dbo.AdoptATurtle AS TA ON GC.GiftCodeID = TA.GiftCodeID LEFT OUTER JOIN

                      dbo.TurtleName AS TN ON TN.TurtleID = TA.TurtleID LEFT OUTER JOIN

                      dbo.LearnItTaken AS LT ON GC.GiftCodeID = LT.GiftCodeID LEFT OUTER JOIN

                      dbo.LearnItName AS LN ON LN.LearnItID = LT.LearnItID LEFT OUTER JOIN

                      dbo.AdoptAPenguin AS PA ON GC.GiftCodeID = PA.GiftCodeID LEFT OUTER JOIN

                      dbo.PenguinName AS PN ON PN.PenguinID = PA.PenguinID

WHERE     (GCH.StatusID = 'REGISTERED') AND (GCH.GiftHistDateAdded >= DATEADD(dd, - 7, CAST(CONVERT(VARCHAR(10), GETDATE(), 120) AS DATETIME))) AND

                       (GCH.GiftHistDateAdded < GETDATE() - 1)

GROUP BY GCT.GiftCertificateTypeName, COALESCE (PT.PonyName, DN.DonkeyName, MN.MonkeyName, BC.BigCatName, TN.TurtleName, PN.PenguinName), 

                      DATEPART(day, GCH.GiftHistDateAdded), DATEPART(month, GCH.GiftHistDateAdded), DATEPART(year, GCH.GiftHistDateAdded)

ORDER BY DATEPART(year, GCH.GiftHistDateAdded) ,DATEPART(month, GCH.GiftHistDateAdded)  ,DATEPART(day, GCH.GiftHistDateAdded)  
 
 

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]

Begin DesignProperties = 

   Begin PaneConfigurations = 

      Begin PaneConfiguration = 0

         NumPanes = 4

         Configuration = "(H (1[40] 4[20] 2[20] 3) )"

      End

      Begin PaneConfiguration = 1

         NumPanes = 3

         Configuration = "(H (1 [50] 4 [25] 3))"

      End

      Begin PaneConfiguration = 2

         NumPanes = 3

         Configuration = "(H (1 [50] 2 [25] 3))"

      End

      Begin PaneConfiguration = 3

         NumPanes = 3

         Configuration = "(H (4 [30] 2 [40] 3))"

      End

      Begin PaneConfiguration = 4

         NumPanes = 2

         Configuration = "(H (1 [56] 3))"

      End

      Begin PaneConfiguration = 5

         NumPanes = 2

         Configuration = "(H (2 [66] 3))"

      End

      Begin PaneConfiguration = 6

         NumPanes = 2

         Configuration = "(H (4 [50] 3))"

      End

      Begin PaneConfiguration = 7

         NumPanes = 1

         Configuration = "(V (3))"

      End

      Begin PaneConfiguration = 8

         NumPanes = 3

         Configuration = "(H (1[56] 4[18] 2) )"

      End

      Begin PaneConfiguration = 9

         NumPanes = 2

         Configuration = "(H (1 [75] 4))"

      End

      Begin PaneConfiguration = 10

         NumPanes = 2

         Configuration = "(H (1[66] 2) )"

      End

      Begin PaneConfiguration = 11

         NumPanes = 2

         Configuration = "(H (4 [60] 2))"

      End

      Begin PaneConfiguration = 12

         NumPanes = 1

         Configuration = "(H (1) )"

      End

      Begin PaneConfiguration = 13

         NumPanes = 1

         Configuration = "(V (4))"

      End

      Begin PaneConfiguration = 14

         NumPanes = 1

         Configuration = "(V (2))"

      End

      ActivePaneConfig = 0

   End

   Begin DiagramPane = 

      Begin Origin = 

         Top = 0

         Left = 0

      End

      Begin Tables = 

         Begin Table = "GCH"

            Begin Extent = 

               Top = 6

               Left = 38

               Bottom = 121

               Right = 224

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "GC"

            Begin Extent = 

               Top = 6

               Left = 262

               Bottom = 121

               Right = 461

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "GCT"

            Begin Extent = 

               Top = 6

               Left = 499

               Bottom = 121

               Right = 744

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "AP"

            Begin Extent = 

               Top = 6

               Left = 782

               Bottom = 91

               Right = 950

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "PT"

            Begin Extent = 

               Top = 6

               Left = 988

               Bottom = 91

               Right = 1156

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "AD"

            Begin Extent = 

               Top = 96

               Left = 782

               Bottom = 181

               Right = 950

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "DN"

            Begin Extent = 

               Top = 96

               Left = 988

               Bottom = 181

               Right = 1156

            End

            DisplayFlags = 280

            TopColumn = 0

         En' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewWeeklyRegistrationsReport'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'd

         Begin Table = "AM"

            Begin Extent = 

               Top = 126

               Left = 38

               Bottom = 211

               Right = 206

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "MN"

            Begin Extent = 

               Top = 126

               Left = 244

               Bottom = 211

               Right = 412

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "ABC"

            Begin Extent = 

               Top = 126

               Left = 450

               Bottom = 211

               Right = 618

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "BC"

            Begin Extent = 

               Top = 186

               Left = 656

               Bottom = 271

               Right = 824

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "TA"

            Begin Extent = 

               Top = 186

               Left = 862

               Bottom = 271

               Right = 1030

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "TN"

            Begin Extent = 

               Top = 186

               Left = 1068

               Bottom = 271

               Right = 1236

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "LT"

            Begin Extent = 

               Top = 216

               Left = 38

               Bottom = 301

               Right = 206

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "LN"

            Begin Extent = 

               Top = 216

               Left = 244

               Bottom = 301

               Right = 412

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "PA"

            Begin Extent = 

               Top = 216

               Left = 450

               Bottom = 316

               Right = 618

            End

            DisplayFlags = 280

            TopColumn = 0

         End

         Begin Table = "PN"

            Begin Extent = 

               Top = 276

               Left = 656

               Bottom = 391

               Right = 861

            End

            DisplayFlags = 280

            TopColumn = 0

         End

      End

   End

   Begin SQLPane = 

   End

   Begin DataPane = 

      Begin ParameterDefaults = ""

      End

      Begin ColumnWidths = 9

         Width = 284

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

         Width = 1500

      End

   End

   Begin CriteriaPane = 

      Begin ColumnWidths = 12

         Column = 1440

         Alias = 900

         Table = 1170

         Output = 720

         Append = 1400

         NewValue = 1170

         SortType = 1350

         SortOrder = 1410

         GroupBy = 1350

         Filter = 1350

         Or = 1350

         Or = 1350

         Or = 1350

      End

   End

End

' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewWeeklyRegistrationsReport'

GO

EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'viewWeeklyRegistrationsReport'

Open in new window

0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24047843
Post the  select satatment
0
 
LVL 1

Accepted Solution

by:
hari_tailor earned 400 total points
ID: 24048101
Create your view without ORDER BY Clause

Aftre creating your view you can see your order by result

select * from viewWeeklyRegistrationsReport order by year, month, day

year, month and day columns are defined in your view
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24048141
I agree with hari_tailor.

explanation: as from sql 2005 (sp1), order by inside of a view is ignored, except for subqueries (which then require a TOP syntax...)
0
 
LVL 8

Assisted Solution

by:vinurajr
vinurajr earned 50 total points
ID: 24048144
View is just a logical representaion of data....
so u View wouldnt get created untill u remove the order by clause from the View creation

this throws an error
Msg 1033, Level 15, State 1, Procedure Test, Line 3
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.

so use Order by in the select
0
 

Author Comment

by:meinIT
ID: 24048634
thanks all.
i have a slight problem, the order is still seems not to be correct...
have used the following statement

SELECT * FROM viewWeeklyWebOrdersReport ORDER BY DAY DESC,MONTH DESC,YEAR  DESC

attached is what i have got
31	3	2009

30	3	2009

30	3	2009

30	3	2009

30	3	2009

28	3	2009

28	3	2009

27	3	2009

26	3	2009

26	3	2009

26	3	2009

26	3	2009

26	3	2009

26	3	2009

26	3	2009

1	4	2009
 

this should rather would have been starting from 26 ..and ending to 1st april
 

please can you still advise?

Open in new window

0
 
LVL 1

Expert Comment

by:hari_tailor
ID: 24048817
your new select statement will first desc day than month then year, so return result as you show
I think your statement like

Year, Month than Day

SELECT * FROM viewWeeklyWebOrdersReport ORDER BY YEAR  DESC, MONTH DESC, DAY DESC
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query 34 79
create insert script based on records in a table 4 12
recover sqlserver db 8 45
MSSQL: Substring and Charindex error 7 0
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now