Solved

ORDER BY Date not working in Views

Posted on 2009-04-02
7
754 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

762 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

19 Experts available now in Live!

Get 1:1 Help Now