0

I have several other SQL commands using NpgSQL all working correctly. Commands like INSERT INTO and DELETE FROM.

However, within the same project, and the same code, I can not get a database to order itself using ORDER BY. If I run the command within Bit.io, it works. Once I copy and paste the command into my C# project, it does not have the correct behavior.

using var con = new NpgsqlConnection(connectionString);
con.Open();
String sql = "SELECT * FROM databaseName ORDER BY columnName";
NpgsqlCommand command = new NpgsqlCommand(sql, con);
command.ExecuteNonQuery();
con.Close();

It is supposed to order columnName in databaseName alphabetically, but what happens is the database is ordered seemingly in an arbitrary way each time that has no relation to the colunn.

If I run ONLY the command SELECT * FROM databaseName, the same behavior occurs.

The code does not have compilation or runtime errors.

Otteko
  • 1
  • 1
  • 1
    _"It is supposed to order columnName in databaseName alphabetically"_ - no, it does not. `ORDER BY` orders the query result set, not the datastore itself. – Guru Stron Oct 09 '22 at 21:25
  • I do not understand. When the command is run within the query editor on bit.io, it does in fact sort the table. Am I missing a step? – Otteko Oct 09 '22 at 21:33
  • You mean query results tab? =) That is not a database that is query results. – Guru Stron Oct 09 '22 at 21:46
  • I don't know. I am just trying to sort the database, the "Query editor" in bit.io allows me to input SQL commands to run against the database. When I run SQL in there or in my C# project, the results are identical, EXCEPT for ORDER BY, which is what I am having the issue with. The ORDER BY within Bit.io's query editor does update the database, but not when executed from my C# project. I do not know why – Otteko Oct 09 '22 at 21:49
  • 1
    For your own sake, find a good SQL or tutorial and learn the basics about how things work. If you don't, you're going to get very frustrated and annoyed for a very long period of time. – Ken White Oct 09 '22 at 22:08

1 Answers1

2

As written in the comments - ORDER BY does not sort the stored data itself, it sorts the data returned by query. Your query actually returns data so you can process it on the application side:

using var con = new NpgsqlConnection(connectionString);
con.Open();
String sql = "SELECT * FROM databaseName ORDER BY columnName";
using NpgsqlCommand command = new NpgsqlCommand(sql, con);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine(reader.GetString(0));
}

Read more:

Guru Stron
  • 102,774
  • 10
  • 95
  • 132
  • Ok, what I need to do is order the database itself, per the instructions given to me. Is there a step I need to do to make that happen? – Otteko Oct 09 '22 at 21:53
  • @Otteko _"what I need to do is order the database itself"_ - but why? It seems that either you are missing something in understanding how databases usually work or it is an [x-y problem](https://xyproblem.info/). Or both. – Guru Stron Oct 09 '22 at 21:56
  • @Otteko databases like PostgreSQL store data in files. Usually the way of how data is stored and ordered is an implementation detail which should not be a concern of application developer. And I'm not sure that all existing database which store data in files have some tools to affect that ordering. – Guru Stron Oct 09 '22 at 22:01