October 13, 2014 at 9:20 am
Hi,
I have a table persons,I need to do order by UpdatedDate.but giving the error as incorrect syantax near order by
CREATE TABLE [dbo].[persons](
[PersonID] [int] NULL,
[LastName] [varchar](10) NULL,
[Address] [varchar](200) NULL,
[UpdatedDate] [datetime] NULL
) ON [PRIMARY] Order By UpdatedDate
GO
SET ANSI_PADDING OFF
GO
Any help...
October 13, 2014 at 9:25 am
You cannot order a table. A table is a set of rows (tuples I think) and has no order.
ORDER BY is a function of data retrieval (SELECT), not storage.
October 13, 2014 at 9:42 am
You can create a clustered index on the UpdatedDate column. This will cause a logical ordering of the rows on disk which may, for example, be helpful if you frequently require your query results sorted on that column. Bear in mind that the clustered index will not guarantee the order in which the rows are retrieved: for that, you need an ORDER BY clause.
John
October 13, 2014 at 11:16 am
Hi,
Thank you for replying soon.
The idea is that i created a new table persons is to log the changes which are updated from frontend.
When ever we update any record in frontend it will fire a trigger and update in persons table.
the trigger which i created is this
ALTER TRIGGER [dbo].[tr_Audit] ON [dbo].[tblemployee]
AFTER UPDATE
AS
Declare @ID char(6),
@Desc char(40),
@XID int
SET NOCOUNT ON
Select @ID = i.UID , @Desc =i.EvDescription ,@XID = i.EvID From Inserted i
If (@Desc ='' or @Desc ='Available' )
Begin
INSERT INTO tblpersons VALUES(@XID, @ID, @Desc , GETUTCDATE())
End
else
Begin
INSERT INTO tblpersons VALUES(@XtID, @ID, @Desc , GETUTCDATE())
End
My question is how can i create a clustered index on Updateddate column in persons table?
October 13, 2014 at 1:29 pm
I don't think you really want to put your clustered index on the UpdateDate, you may want a non-clustered index, but not the clustered index. Here's a good read on clustered indexes, http://www.sqlskills.com/blogs/kimberly/ever-increasing-clustering-key-the-clustered-index-debate-again/
You really shouldn't be concerned with the order the data is stored in, you want to be concerned with how the data is retrieved. So you just want to add an ORDER BY to your SELECT so your application gets the data in the order it expects. I'd even argue that ordering should be handled by the application.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2014 at 1:41 pm
Thank you champion, when ever we want to see the results form log table we can simply do select with order by date..right.?
October 13, 2014 at 1:50 pm
mcfarlandparkway (10/13/2014)
Thank you champion, when ever we want to see the results form log table we can simply do select with order by date..right.?
Yes
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2014 at 2:37 pm
select
lastname
, updateddate
from log
order by updateddate
is what you can do.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply