What Is the Purpose of Order by 1 in SQL Select Statement

What is the purpose of Order By 1 in SQL select statement?

This:

ORDER BY 1

...is known as an "Ordinal" - the number stands for the column based on the number of columns defined in the SELECT clause. In the query you provided, it means:

ORDER BY A.PAYMENT_DATE

It's not a recommended practice, because:

1. It's not obvious/explicit
2. If the column order changes, the query is still valid so you risk ordering by something you didn't intend

ORDER BY 1,2,3,4

Sort by ordinal positions of columns

SQL Server allows you to sort the result set based on the ordinal positions of columns that appear in the select list.

The following statement sorts the customers by first name and last name. But instead of specifying the column names explicitly, it uses the ordinal positions of the columns:

SELECT
    first_name,
    last_name
FROM
    sales.customers
ORDER BY
    1,
    2;

In this example, 1 means the first_name column and 2 means the last_name column.

Using the ordinal positions of columns in the ORDER BY clause is considered as bad programming practice for a couple of reasons.

1. First, the columns in a table don’t have ordinal positions and need to be referenced by name.
2. Second, when you modify the select list, you may forget to make the corresponding changes in the ORDER BY clause.

Therefore, it is a good practice to always specify the column names explicitly in the ORDER BY clause.

For more details, go Here

What does SQL clause GROUP BY 1 mean?

It means to group by the first column of your result set regardless of what it's called. You can do the same with ORDER BY.

what does order 1,2 do

It sorts the result by the first and second column, so in your case it's identical to

select *
from t
order by item, bin;

What does it mean by select 1 from table?

SELECT 1 FROM TABLE_NAME means, "Return 1 from the table". It is pretty unremarkable on its own, so normally it will be used with WHERE and often EXISTS (as @gbn notes, this is not necessarily best practice, it is, however, common enough to be noted, even if it isn't really meaningful (that said, I will use it because others use it and it is "more obvious" immediately. Of course, that might be a viscous chicken vs. egg issue, but I don't generally dwell)).

 SELECT * FROM TABLE1 T1 WHERE EXISTS (
     SELECT 1 FROM TABLE2 T2 WHERE T1.ID= T2.ID
 );

Basically, the above will return everything from table 1 which has a corresponding ID from table 2. (This is a contrived example, obviously, but I believe it conveys the idea. Personally, I would probably do the above as SELECT * FROM TABLE1 T1 WHERE ID IN (SELECT ID FROM TABLE2); as I view that as FAR more explicit to the reader unless there were a circumstantially compelling reason not to).

EDIT

There actually is one case which I forgot about until just now. In the case where you are trying to determine existence of a value in the database from an outside language, sometimes SELECT 1 FROM TABLE_NAME will be used. This does not offer significant benefit over selecting an individual column, but, depending on implementation, it may offer substantial gains over doing a SELECT *, simply because it is often the case that the more columns that the DB returns to a language, the larger the data structure, which in turn mean that more time will be taken.

What is the purpose of using WHERE 1=1 in SQL statements?

Yeah, it's typically because it starts out as 'where 1 = 0', to force the statement to fail.

It's a more naive way of wrapping it up in a transaction and not committing it at the end, to test your query. (This is the preferred method).

Why does SELECT TOP 1 . . . ORDER BY return the second row in the table?

In relational databases tables have no inherent order. The ORDER BY you give is not distinct over all records, in fact it's the same over all records. So the order in which results are returned is still not deterministic and unpredictable. And therefor the top 1 returns an unpredictable row.

You say "Adam's details are first in the table", this is simply not true; records in a table are stored without any order. If you select without an order by or (as in your case) the order by is not deterministic the returned order is arbitrary.

Why and How do ORDER BY CASE Queries Work in SQL Server?

How does this work?

ORDER BY (CASE WHEN col2 IS NULL THEN 1 ELSE 0 END),
         col2;

Well, it works exactly as the code specifies. The first key for the ORDER BY takes on the values of 1 and 0 based on col2. The 1 is only when the value is NULL. Because 1 > 0, these are sorted after the non-NULL values. So, all non-NULL values are first and then all NULL values.

How are the non-NULL values sorted? That is where the second key comes in. They are ordered by col2.

What does TOP 1 mean in an sql query?

The query in the example will return the first RequestID from the table PublisherRequests.

The order of the results without an Order By clause is arbitrary. So, your example will return an arbitrary RequestID (i.e. the first RequestID in an arbitrarily ordered list of RequestIDs).

You can change the order by defining an Order By.

For example, to get the last entered ID, you can write

Select Top 1 RequestID
From PublisherRequests
Order By RequestID Desc

Updated to include corrected order information from @Kirtan Gor and @AlexK

---

Related Topics