1

I am working on Windows Form Application and it accesses database in SQL Server 2014. I have EmployeeTable which I retrieve data from, and display all the records in DataGridView. In this table, I have a column SequenceID, which basically increments from 1 up to the number of records in this table, but this is not the same as AUTO INCREMENT in that SequenceID gets updated each time the table is modified, and keeps the numerical order no matter how many times new records get inserted or some records are deleted. For example, if the data looks like

SequenceID | Name
     1     | John
     2     | Mary
     3     | Robert

and Mary is removed, then the resulting table needs to look like 

SequenceID | Name
     1     | John
     2     | Robert

In order to achieve this, I used the best answer by zombat from Update SQL with consecutive numbering, and it was working great until I used ORDER BY expression.

This EmployeeTable also has DateAdded column, containing the date when the record was inserted. I need to display all records ordered by this DateAdded column, with the oldest record shown at the top and the newest at the bottom in addition to the correct SequenceID order. However, it gets messed up when a record is deleted, and a new one is inserted.

If I insert 3 records like, 

SequenceID | Name   | DateAdded
     1     | John   | 9/25/2017
     2     | Mary   | 9/26/2017
     3     | Robert | 9/27/2017

and remove Mary, it becomes 

SequenceID | Name   | DateAdded
     1     | John   | 9/25/2017
     2     | Robert | 9/27/2017

and this is good so far. However, if I add another record Tommy on, say, 9/28/2017, which should be added at the bottom because it is the newest, it results in something like,

SequenceID | Name   | DateAdded
     1     | John   | 9/25/2017
     3     | Robert | 9/27/2017
     2     | Tommy  | 9/28/2017

The ORDER BY is working fine, but it messes up the SequenceID, and I am not sure why this is happening. All I am doing is, 

SELECT * 
FROM EmployeeTable 
ORDER BY DateAdded

I tried placing zombat's SQL command both before and after this SQL command, but neither worked. It seems to me like when I delete a row, the row has an invisible spot, and a new record is inserted in there.

Is there any way to fix this so I can order the records by DateAdded and still have the SequenceID working correctly?

J.Doe
  • 329
  • 3
  • 14
  • When you order by `DateAdded` , you order by `DateAdded` not `SequenceId` – Lukasz Szozda Oct 09 '17 at 17:15
  • I know. What I am trying to do is, order them by `DateAdded` and then update the `SequenceID`. I am trying to update the `SequenceID`, but it is not getting updated.. – J.Doe Oct 09 '17 at 17:17

1 Answers1

2

If you need id for GUI (presentation only) you could use:

SELECT ROW_NUMBER() OVER(ORDER BY DateAdded) AS sequenceId, Name, DateAdded
FROM EmployeeTable 
ORDER BY DateAdded;

EDIT:

I am trying to update the SequenceID, but it is not getting updated

You should not try to reorder your table every time. It doesn't make sense.

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Thank you. Your SQL command seems to be working when I just tried it. What I was doing was, after a table is modified by either deleting/adding a row, I ran `zombat`'s SQL command to fix the `SequenceID` but it was not working. – J.Doe Oct 09 '17 at 17:21
  • Actually it is not working.. It seems like this SQL command you provided is temporarily changing the order of `SequenceID` for display, but the actual `sequenceID` is not changed in the table. When I tried to remove a row of a certain `SequenceID`, it removed a completely different row which actually had that `sequenceID` – J.Doe Oct 09 '17 at 17:53
  • 1
    Actually I got it figured out by adding a new column. Thanks for your knowledge! – J.Doe Oct 09 '17 at 18:28