CODEWITHSUNDEEP
sqlsql-serverdatabasesorting
iarf
iarf

Reputation: 11

Creating a column of an SQL table to number records based on their order

I have a web program (PHP and JavaScript) that needs to display entries in the table based on how recently they were added. To accomplish this I want to have one column of the table represent the entry number while another would represent what it should be displayed at.

For example, if I had 10 records, ID=10 would correspond to Display=1 . I was wondering if there would be a simple way to update this, ordering by the entry ID and generating the display IDs accordingly.

Upvotes: 0

Views: 232

Answers (3)

Karl Kieninger
Karl Kieninger

Reputation: 9129

There are lots of ways to do this. As the others have noted, it wouldn't be common practice to store the reverse or inverted id like this. You can get the display_id several ways. These come to mind quickly:

CREATE TABLE test (entry_id INT)
GO
INSERT INTO test VALUES (1),(2),(3)
GO

--if you trust your entry_id is truly sequential 1 to n you can reverse it for the display_id using a subquery
SELECT entry_id,
       (SELECT MAX(entry_id) + 1 FROM test) - entry_id display_id 
FROM test

--or a cte
;WITH cte AS (SELECT MAX(entry_id) + 1  max_id FROM test)  
 SELECT entry_id,  
       max_id - entry_id display_id     
  FROM test
       CROSS APPLY 
       cte

--more generally you can generate a row easily since sql server 2005
SELECT entry_id
      ,ROW_NUMBER() OVER (ORDER BY entry_id DESC)  display_id
  FROM test

You could use any of those methods in a view to add display id. Normally I'd recommend you just let you presentation layer handle the numbering for display, but if you intend to query back against it you might want to persist it. I can only see storing it if the writes are infrequent relative to reads. You could update a "real" column using a trigger. You could also create a persisted computed column.

Upvotes: 0

Tobi
Tobi

Reputation: 31479

Why aren't you making use of SQLServer's default column values? Have a look here to see an example: Add default value of datetime field in SQL Server to a timestamp

For example, you have a table like this:

create table test (
    entry_id number,
    message varchar(100),
    created_time datetime default GETDATE()
);

Then you can insert like

insert into test values (1, "test1");
insert into test values (2, "test2");

And select like

select entry_id, message from test order by created_time desc

Upvotes: 1

vogomatix
vogomatix

Reputation: 5041

Your question is a little vague, but here goes....

Normally IDs ascend, with the highest ID being the most recently added, so you can ORDER by ID desc in your query to determine which should be displayed. The results you get from the query will be the display order.

Upvotes: 1

Related Questions