Benefits Of Using SQL Ordinal Position Notation?
I'd use it:
- If you love troubleshooting
- Creating adhoc queries without intellisense
There is no upside.
SQL Server only supports in the ORDER BY anyway. Anywhere else it's an expression to be evaluated.
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:
- It's not obvious/explicit
- If the column order changes, the query is still valid so you risk ordering by something you didn't intend
Which data type to use for ordinal?
"This could fail if somewhere down the line you end up with consecutive integers."
For this (probably rare and thus not performance important) case, you could implement a renumber method that spaces out again. When I used to program in COMAL (anyone know that language?), you could do this very thing with line numbers.
sql, database query
Not sure what you wanted to do. And I sincerely expect you don't do what you're doing. But if you jsut wanted to know how to play with technicality. Here is an example to do it. There may be better ways.
mysql> desc test;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from test;
+------+-------+
| id | name |
+------+-------+
| 1 | name1 |
| 2 | name2 |
| 3 | name3 |
| 4 | name4 |
+------+-------+
4 rows in set (0.00 sec)
mysql> select @colid:=column_name from information_schema.columns where table_schema='test' and table_name='test' and ordinal_position=2;
+---------------------+
| @colid:=column_name |
+---------------------+
| name |
+---------------------+
1 row in set (0.01 sec)
mysql> set @sqlstr:=concat('select ', @colid, ' from test');
Query OK, 0 rows affected (0.00 sec)
mysql> prepare sttmt from @sqlstr;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
mysql> execute sttmt;
+-------+
| name |
+-------+
| name1 |
| name2 |
| name3 |
| name4 |
+-------+
4 rows in set (0.00 sec)
refer the last example here http://dev.mysql.com/doc/refman/5.0/en/user-variables.html
By the way, Google gives first link as perfect solution. Select statement with column number instead of column names
What do comma-separated integers in a GROUP BY statement accomplish?
It is equivalent to writing:
SELECT col1, col2, col3, col4, col5, SUM(col6) AS total
FROM table_name
WHERE col1 < 99999
GROUP BY col1, col2, col3, col4, col5
The numbers are the values/columns in the select-list expressed by ordinal position in the list, starting with 1.
The numbers used to mandatory; then the ability to use the expressions in the select-list was added. The expressions can get unwieldy, and not all DBMS allow you to use 'display labels' or 'column aliases' from the select-list in the GROUP BY clause, so occasionally using the column numbers is helpful.
In your example, it would be better to use the names - they are simple. And, in general, use names rather than numbers whenever you can.
Why use the BETWEEN operator when we can do without it?
BETWEEN
can help to avoid unnecessary reevaluation of the expression:
SELECT AVG(RAND(20091225) BETWEEN 0.2 AND 0.4)
FROM t_source;
---
0.1998
SELECT AVG(RAND(20091225) >= 0.2 AND RAND(20091225) <= 0.4)
FROM t_source;
---
0.3199
t_source
is just a dummy table with 1,000,000
records.
Of course this can be worked around using a subquery, but in MySQL
it's less efficient.
And of course, BETWEEN
is more readable. It takes 3
times to use it in a query to remember the syntax forever.
In SQL Server
and MySQL
, LIKE
against a constant with non-leading '%'
is also a shorthand for a pair of >=
and <
:
SET SHOWPLAN_TEXT ON
GO
SELECT *
FROM master
WHERE name LIKE 'string%'
GO
SET SHOWPLAN_TEXT OFF
GO
|--Index Seek(OBJECT:([test].[dbo].[master].[ix_name_desc]), SEEK:([test].[dbo].[master].[name] < 'strinH' AND [test].[dbo].[master].[name] >= 'string'), WHERE:([test].[dbo].[master].[name] like 'string%') ORDERED FORWARD)
However, LIKE
syntax is more legible.
MySql queries: really never use SELECT *?
SELECT * FROM ... is not always the best way to go unless you need all columns. That's because if for example a table has 10 columns and you only need 2-3 of them and these columns are indexed, then if you use SELECT * the query will run slower, because the server must fetch all rows from the datafiles. If instead you used only the 2-3 columns that you actually needed, then the server could run the query much faster if the rows were fetch from a covering index. A covering index is one that is used to return results without reading the datafile.
So, use SELECT * only when you actually need all columns.
SQL Query Shortcuts
See Aaron Bertrand's "Bad Habits in SQL to kick" series - he has a post on this and highly recommends not using that notation:
- Bad habits to kick : ORDER BY ordinal
Related Topics
Error: Functions in Index Expression Must Be Marked Immutable in Postgres
How to Do an Inner Join on Row Number in SQL Server
SQL Script to Find Invalid Email Addresses
Can Select * Usage Ever Be Justified
How to Write a SQL Delete Statement with a Select Statement in the Where Clause
T-SQL How to Create Tables Dynamically in Stored Procedures
Alternatives to Replace on a Text or Ntext Datatype
The Most Elegant Way to Generate Permutations in SQL Server
In Which Sequence Are Queries and Sub-Queries Executed by the SQL Engine
SQL Server: Get Total Days Between Two Dates
Is There a SQL Implementation of Pbkdf2
Combine Two Tables for One Output
SQL Comments on Create Table on SQL Server 2008
SQL Server Update Trigger, Get Only Modified Fields
Achieving Row_Number/Partition by in Ms Access
Foreign Key Not Populating with Primary Key Values
Merge Row Values into a CSV (A.K.A Group_Concat for SQL Server)