Solved

ORDER BY Date not working in Views

Posted on 2009-04-02
7
760 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
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.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

803 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