0

enter image description hereI need to insert a row at the top of my table. Is there a way to do that?

I am aware of "Order By" and that is not going to work. I need to change the actual table (Please do not suggest Order By. Most of the threads I found here only suggests that).

The column contains dates. The table is already ordered by desc when the data is input. Everyday, I need to add a row that has the current date and populate the rest of the data based on that. That row has to be on the top.

I have an ID Column. It is not a primary key. It goes 1, 2, 3... The new date that is added should ideally be inserted on row 1 with it's ID being 1 (That's not absolutely vital though). I am okay with ID being all over the place but the dates have to be all descending.

Edit: I have added screenshots of the table before insert and after. The table has some 300 rows of data. As you can see, after the insert, the current date goes to the bottom. I want to change that.

Please help. Thanks

Fariya Farhad
  • 33
  • 2
  • 9
  • Add a few rows of sample table data, both before and after the operation. (As well formatted text.) – jarlh Dec 02 '16 at 15:17
  • Just added a screenshot of the table data before and after the operation. Did you need data in an actual table format? – Fariya Farhad Dec 02 '16 at 15:28
  • 2
    Data is stored unordered. There aren't really any first or last rows. To read rows in a specific order you use ORDER BY. ID's are just ID's, and they aren't supposed to mean anything. But changing ID's between rows will mess up foreign keys and stuff - so don't do it. – jarlh Dec 02 '16 at 15:36
  • There is no foreign key in this table. And once again, I need to know if there is a way it can be done. Not just displayed using ORDER BY. Thanks – Fariya Farhad Dec 02 '16 at 16:04
  • Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Dec 02 '16 at 16:07
  • Hi marc_s, it is for sql server – Fariya Farhad Dec 02 '16 at 16:37

1 Answers1

0

If you read the following response to a question asked previously, it will detail why you must use order by due to the order of the rows being returned never being guaranteed:

Default row order in SELECT query

Therefore just insert the data into the table, what SQL does with it in the background is irrelevant to you, what is relevant is the order in which you request them to be retrieved. Therefore Order By is the way to get the data out in the order that you want.

I'm struggling to understand what you mean by:

The table is already ordered by desc when the data is input.

It should be a two stage process:

1) Insert Data

INSERT INTO [TABLENAME] VALUES ([ID], [risk_date_day], [Risk1], [Risk2], [Risk3], [Risk4], [Risk5], [Grand Total], [1Per], [2Per], [3Per], [4Per], [5Per]);
--Naturally replace the values between square brackets with the correct values for data entry.

2) Retrieve Data

SELECT [ID], [risk_date_day], [Risk1], [Risk2], [Risk3], [Risk4], [Risk5], [Grand Total], [1Per], [2Per], [3Per], [4Per], [5Per]
FROM [TABLENAME]
ORDER BY [risk_date_day] DESC;

This will return the data with the latest date at the top.

However if you are in fact saying that the date you could enter on any particular day may not necessarily be the latest date, then this will not work. In which case I would suggest adding a field called DateOfEntry and use that field to do the Order By.

Community
  • 1
  • 1
Landy81
  • 16
  • 3
  • Hi Landy81, When I say "the table is already ordered by desc when the data is input"... I created this table (A) from another table. When I was querying the data from the other table, I inserted into my first table A in order (by date descending). Now when I am adding rows to it, it goes to the bottom of course. That was what I was trying to prevent – Fariya Farhad Dec 02 '16 at 16:56
  • @FariyaFarhad It will do and this should be irrelevant to you. How it is stored is SQL's concern, you should only worry about how you get the data back out of the database, via the select statement. – Landy81 Dec 02 '16 at 17:04
  • @Takarii it was a different question but the answer is relevant to this case. – Landy81 Dec 02 '16 at 17:05
  • Alright. Seems like that is the best thing to do. Thanks for all your help! – Fariya Farhad Dec 02 '16 at 17:06