• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 780
  • Last Modified:

ORDER BY Date not working in Views

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
meinIT
Asked:
meinIT
3 Solutions
 
meinITAuthor Commented:
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
 
udaya kumar laligondlaTechnical LeadCommented:
Post the  select satatment
0
 
hari_tailorCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
vinurajrCommented:
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
 
meinITAuthor Commented:
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
 
hari_tailorCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now