Inserting a record into a certain spot in a table

Question

Inserting a record into a certain spot in a table

itmasterw 60042

SSCrazy

Points: 2852
More actions
July 27, 2018 at 1:15 pm

#373920

Hi,
I have a table that hold names of reports, and these names are shown in an grid view in a VB.Net application.
sometimes the user would like to see csertian name after another.
So for example:
Boats
Saleboat
Rowboat
Cars
Cevi
Ford
Honda Crv
Honda Civic
Honda piolet
Now suppose I wanted to put Honda HRV in between Honda Crv and Honda Civic.
Cars
Cevi
Ford
Honda Crv
Honda HRV
Honda Civic
Honda piolet
Is there a way that I can do this so that in the application it would show up like this through SQL?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 1

itmasterw 60042 - Friday, July 27, 2018 1:15 PM
Hi,
I have a table that hold names of reports, and these names are shown in an grid view in a VB.Net application.
sometimes the user would like to see csertian name after another.
So for example:
Boats
Saleboat
Rowboat
Cars
Cevi
Ford
Honda Crv
Honda Civic
Honda piolet
Now suppose I wanted to put Honda HRV in between Honda Crv and Honda Civic.
Cars
Cevi
Ford
Honda Crv
Honda HRV
Honda Civic
Honda piolet
Is there a way that I can do this so that in the application it would show up like this through SQL?
Thank you

Yes and no. Yes, it can be done if the table and the query is designed to allow such an insertion. No, simply based on the extremely limited information provided.
Please remember that a table is actually an unordered set of data. You apply order based on an ORDER BY clause on the query retrieving the data. Do not rely on any indexing to ensure order.

drew.allen SSC Guru Points: 76776 More actions · Answer 2

itmasterw 60042 - Friday, July 27, 2018 1:15 PM
Hi,
I have a table that hold names of reports, and these names are shown in an grid view in a VB.Net application.
sometimes the user would like to see csertian name after another.
So for example:
Boats
Saleboat
Rowboat
Cars
Cevi
Ford
Honda Crv
Honda Civic
Honda piolet
Now suppose I wanted to put Honda HRV in between Honda Crv and Honda Civic.
Cars
Cevi
Ford
Honda Crv
Honda HRV
Honda Civic
Honda piolet
Is there a way that I can do this so that in the application it would show up like this through SQL?
Thank you

Tables represent sets, which are unordered by definition. You can't insert a row in a specific position, because the idea of a specific position is meaningless in this context. The only way to guarantee an order is with an ORDER BY clause.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 3

Thanks for the replies, I thought of the order by and I know that there really is not a guarantied order but with out an order by this is how it comes out.
That is I have boats and list of boats cars and a list of cars. So Order by will not work here.
However, the table is seeded with an identity column, can I do anything with that?
Or is there another was I can structure the table to be able to do this?
Thank you

Lynn Pettis SSC Guru Points: 442447 More actions · Answer 4

itmasterw 60042 - Friday, July 27, 2018 1:46 PM
Thanks for the replies, I thought of the order by and I know that there really is not a guarantied order but with out an order by this is how it comes out.
That is I have boats and list of boats cars and a list of cars. So Order by will not work here.
However, the table is seeded with an identity column, can I do anything with that?
Or is there another was I can structure the table to be able to do this?
Thank you

Again, not enough information. We can't see what you see. If the order of items is as listed without an order by, it is relying on the order the data is inserted or by an internal key (maybe even the clustered index) but that does not ensure the order as more data is inserted. The only way to ensure order is with an ORDER BY clause on a specified column or columns depending on the data. For instance, a manually maintained DisplayOrder column that provides the specific order of the data to be displayed.

Without more information about the table involved here, meaning the DDL (CREATE TABLE statement) for the table, sample data for the table and expected results, we can't be of much help.

drew.allen SSC Guru Points: 76776 More actions · Answer 5

itmasterw 60042 - Friday, July 27, 2018 1:46 PM
Thanks for the replies, I thought of the order by and I know that there really is not a guarantied order but with out an order by this is how it comes out.
That is I have boats and list of boats cars and a list of cars. So Order by will not work here.
However, the table is seeded with an identity column, can I do anything with that?
Or is there another was I can structure the table to be able to do this?
Thank you

Again, the ONLY way to guarantee an order is with an ORDER BY clause. The fact that the results have been returned in a specific order in the past is no guarantee that they will continue to be returned in that order in the future. If you want them returned in a specific order you must supply an ORDER BY clause.

An ORDER BY clause MUST work here. You may have to add new columns, computed fields, CASE expressions, or supplemental tables to determine the correct order, but there is always a way to specify an order. If you supply the requested data, we can help you formulate the correct ORDER BY clause.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

itmasterw 60042 SSCrazy Points: 2852 More actions · Answer 6

Hi and thanks again for your replies. Sorry, I did not understand what you wanted when you said you needed more information; I should have thought of scripting it for you, sorry.
Anyway, I got some ideas from you guys, on what I might be able to do.
Thank you