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

Question

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.

score 1 · Answer 1 · edited May 23 '17 at 12:13

1

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

edited May 23 '17 at 12:13

Community

1
1

answered Mar 13 '14 at 14:46

Tobi

31,405
8
58
90

This could work in place of the 'entryID' but I need to have static relative values for the new entries in the sense that '1' will always we representative of the newest entry, '2' will be the second latest, etc. – iarf Mar 13 '14 at 15:15
I don't really understand why you would need to do this. Can't an ORDER BY achieve what you want? To be honest, that doesn't really make sense to update all records only because one new record is added. – Tobi Mar 13 '14 at 15:19

score 1 · Answer 2 · answered Mar 13 '14 at 14:57

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.

score 0 · Answer 3 · answered Mar 13 '14 at 16:10

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.

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

3 Answers3