CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Umapathy S
Umapathy S

Reputation: 230

Get data like in database order without sorting it

I have a table which has ShipCountry, ShipCity and Freight column in SQL database. I tried to retrieve data from that table by using the below query. 

Select ShipCountry from CountryDetails Group by ShipCountry

If i run this query i am getting results in Ascending order. Instead of this i need data in database order. How to achieve this through SQL query?

Note: If i run the below query, it will return the data in Database order. I am getting sorted data when i added group by clause in my query. 

Select ShipCountry from CountryDetails

Upvotes: 0

Views: 1909

Answers (3)

SchmitzIT
SchmitzIT

Reputation: 9552

As already has been stated, what you describe might lead to unexpected results fro your end users.

Let's assume you have a table without any indexes or keys (A so-called heap). A heap pretty much can be compared to a phone book (yeah, I've been around for a while) consisting of hundreds of pages, on which information is randomly ordered. A heap is exactly that; A lot of randomly ordered data. Whenever you query from such a table, the query analyzer will do its very best to figure out what the fastest way to deliver the data is.

Such decisions from the query analyzer are guided by statistics; a collection of metrics about the data and the distribution thereof. SQL Server uses these statistics to figure out the cardinality (the uniqueness of values), and thus pick the fastest way to return data.

When you simply issue a SELECT * FROM myTable on a heap, those statistics will determine the order in which your data is returned. However, this also means that over time, the statistics will change, as more data flows into the table. This has the effect that the sort order of your data today is not necessarily the sort order in which the data is returned tomorrow, or even five minutes from now.

If that is fine with your end users, then a SELECT * FROM myTable is the right solution for you. But, if you absolutely need to have the data returned in a certain order, you should always implement an ORDER BY clause.

Upvotes: 2

ScaisEdge
ScaisEdge

Reputation: 133360

The use of group by for ordering is improper .. (group by is for aggregation function as min, max or count)

if you need a specific order use order by instead 

Select ShipCountry from CountryDetails Order  by ShipCountry

otherwise if want not order use simply

Select ShipCountry from CountryDetails

Remember that the values store in db have not a proper order ..and are selected in the sequence used for retrive the data.

Each time you need an order you must esplicitally use order by

for avoid "redundant values" .. use distinct and not group by eg:

Select distinct ShipCountry from CountryDetails

Upvotes: 6

Hope Mystery
Hope Mystery

Reputation: 338

if you want to have the same database order in most cases if you have sorted by primary key it will be the same without ordering as you say:

here the id is the primary key, and if you can not use the primary key add an identity column and use it:

id name

1 elly

2 ahmad

3 joseph

4 omar

Upvotes: 0

Related Questions