select with order by

Asked

Viewed 57 times

6

I have a simple table with only one text field, no field auto increment and indexes

CREATE TABLE teste (Nome varchar(30) NOT NULL)

I inserted the information in this table in the following order:

INSERT INTO teste(Nome) VALUES('C')
INSERT INTO teste(Nome) VALUES('D')
INSERT INTO teste(Nome) VALUES('B')
INSERT INTO teste(Nome) VALUES('A')

If I make a select sorted by name will return the sort by name, ok.

select * from teste order by nome - resultado: A,B,C,D

What I need to do is a select that returns to me the order that was entered, i.e., that the expected result is C,D,B,A.

Is it possible to get this result in a simple table like this? Is there a hidden index that can do this order by?

database sql-server

  • I think the best way would be to create a field with auto increment and order by that field

  • Are you DBA? if yes, put an auto increment field, it will be better for your bank’s performance than doing a magic with order by

  • 1

    A table without a primary key is a bad idea, even if you don’t make "direct" use of it. Create a field with a numeric key and Identity and sort by it, as suggested by @Sorack, without that information you will not know the order they were entered. Still I suggest you have a date field, with default value getdate(). The seed of Identity can be changed, which will mess up your search if it happens

  • Has any response helped solve the problem and can address similar questions from other users? If so, make sure to mark the answer as accepted. To do this just click on the left side of it (below the indicator of up and down votes).

1 answer

5

You can get this result in a simple table like this?

Not

Is there any hidden index that can do this order by?

Not

From the SQL Server 2007 you can use a column IDENTITY. Change your table and add a column with sequence:

ALTER TABLE teste
  ADD sequencia INT IDENTITY;

And select using the ORDER BY:

SELECT t.Nome
  FROM tabela t
 ORDER BY t.sequencia;

IDENTITY

Creates an identity column in a table. This property is used with the instructions CREATE TABLE and ALTER TABLE Transact-SQL.

Like the Ricardo Pontual observed:

Just one remark: use the field IDENTITY with the clear purpose of knowing the order of insertion of the records may fail if the seed of the IDENTITY is changed or restarted for example, which is rare for someone to do, but can happen.

  • only one remark: use the Identity field with the clear purpose to know the order of entering the records may fail if the Identity seed is changed or restarted for example, which is rare for someone to do, but it can happen. A date/time field would be safer from this point of view

  • @Ricardopunctual I cogited the use of a DATETIME, however the use of resource is greater, if the date of the server suffer change the column will be impaired as well and, although the chance of happening is extremely low due to the granularity of the time, there is the possibility of simultaneous insertion. A IDENTITY do not have the same problems. I will add your observation the answer, OK?

  • Perfect, the best performance solution is the same of an int field as Identity of your answer, only put as observation because sometimes people can change the Identity,e ai can mess the ordering

Browser other questions tagged database sql-server

You are not signed in. Login or sign up in order to post.