Behavior of order by clause asc on a serial column
Hi,
There are two tables one is emp and other is dept where is emp_id and dept_id
are serial, schema of the tables are given below and i have following select
statement
Select first 1 e.emp_id,e.emp_name,d.dept_name,d.dept_loc
from emp e,dept d
where e.dept_id=d.dept_id
and e.emp_join_date = today
order by e.emp_id asc
I have two questions related to the select statement
1). Is 'order by e.emp_id ASC' clause required for fetching first row of the
data in ascending order?
2). I am expecting that if order by clause eliminates from the query data will
come in sorted order on emp_id, because its a serial column. please confirm i
am thinking right or wrong?
Please guid me . I am waiting your suggestions in this regards.
Table Schema
------------
create table emp
(emp_id serial not null,
emp_name varchar(20),
emp_add varchar (40),
emp_join_date date,
dept_id integer not null,
primary key (emp_id) constraint pk_emp_id
);
alter table emp add constraint (foreign key (dept_id)
references dept );
create table dept
(dept_id serial not null,
dept_name varchar(20),
dept_loc varchar(20),
dept_desc varchar(100),
primary key (dept_id) constraint pk_dept_id);
The ANSI SQL standard explicitly states that the order of returned rows is
NOT guaranteed UNLESS there is an ORDER BY clause. PERIOD, end of
discussion! Might the data come back in the expected order without the
ORDER BY clause? Yes. WILL IT? Can't say. By ANSI rules the server is
free to use any method it wants to to accomplish the join and the rows could
be returned in any order they happen to be encountered in. Just put the
f(*&^$@! ORDER BY clause in there. OK? Any GOOD reason NOT to?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Jan 19, 2011 at 5:53 AM, ABRAR RASHID <mabrar@i2cinc.com> wrote:
> Hi,
> There are two tables one is emp and other is dept where is emp_id and
> dept_id
> are serial, schema of the tables are given below and i have following
> select
> statement
>
> Select first 1 e.emp_id,e.emp_name,d.dept_name,d.dept_loc
> from emp e,dept d
> where e.dept_id=d.dept_id
> and e.emp_join_date = today
> order by e.emp_id asc>
> I have two questions related to the select statement
>
> 1). Is 'order by e.emp_id ASC' clause required for fetching first row of
> the
> data in ascending order?
> 2). I am expecting that if order by clause eliminates from the query data
> will
> come in sorted order on emp_id, because its a serial column. please confirm
> i
> am thinking right or wrong?
>
> Please guid me . I am waiting your suggestions in this regards.
>
> Table Schema
> ------------
> create table emp
> (emp_id serial not null,
> emp_name varchar(20),
> emp_add varchar (40),
> emp_join_date date,
> dept_id integer not null,
> primary key (emp_id) constraint pk_emp_id
> );>
> alter table emp add constraint (foreign key (dept_id)>
> references dept );
>
> create table dept
> (dept_id serial not null,
> dept_name varchar(20),
> dept_loc varchar(20),
> dept_desc varchar(100),
> primary key (dept_id) constraint pk_dept_id);>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--485b393aafa582769e049a312be5