1

I have this query

 select s.emp_no, min(s.from_date)
 from
 **start** (select s.emp_no
 from salaries s, employees e
 where s.to_date=(select max(to_date) from salaries) and s.emp_no=e.emp_no
 group by s.salary DESC
 LIMIt 10) **end** as emps, salaries s
 where emps.emp_no=s.emp_no
 group by s.emp_no;

The start - end part is correct. As you can see it has just one field that contains the employee's number. The emp_no is foreign key to the table salaries. Table salaries has 2 fields. The emp_no and the from_date. Each emp_no may have more than one from_date values. I want to group all the rows of each emp_no from salaries and keep the min from date_value. This shows corrects results (I suppose), but changes the order of the rows created at the start - end part. Any ideas on keeping the order?

Billy Grande
  • 577
  • 3
  • 11
  • 23
  • 1
    You may need to add a sample and a desired result, although if you want a specific order, you'll need to order by in the outer query too. Any un-ordered outer query executed on the result of an ordered subquery will potentially rearrange the result into a pseudo random order. – Joachim Isaksson Jun 07 '14 at 10:41
  • So, how do I keep the order of the outer as it is in the inner? – Billy Grande Jun 07 '14 at 11:06
  • To answer that, you'll need to add some sample data and a desired result, it's not quite clear how you want a result grouped by something to order exactly as the original result. – Joachim Isaksson Jun 07 '14 at 11:48
  • possible duplicate of [Why do results from a SQL query not come back in the order I expect?](http://stackoverflow.com/questions/10999913/why-do-results-from-a-sql-query-not-come-back-in-the-order-i-expect) – David Manheim Dec 24 '14 at 22:50

1 Answers1

2

You don't have an ORDER BY clause. Without an ORDER BY clause you can not expect your result set to have a specific order. If you want your data to come out in a specific order then you need to order it that way. That may mean calculating a column in your inner table expression to allow you to order the outer result set properly. How you actually achieve the ordering depends on your data and what order you need it to be in.

For more info see:

The difficult concept is this. SQL specifications say that the ordering of result set rows is unpredictable unless completely determined by an ORDER BY clause. This unpredictability is a tricky concept to work with. If you leave out proper ORDER BY specifications from your query, your rows will sometimes, but not always appear in the order you expect. Often, the ordering will change when you move your app from a development to a production server. Worse yet, it may change after months in production when some table in your production server crosses a size threshold. Please be careful.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Mike D.
  • 4,034
  • 2
  • 26
  • 41
  • 2
    True that. Sometimes, ***but not always***, an unintended side-effect of `GROUP BY` is to put the result set in a particular order. – O. Jones Jun 07 '14 at 12:23
  • That may be true but why would you rely on undocumented, unintended consequences when you could explicitly state that you need the results ordered a specific way? There are so many ways that could fall apart I couldn't recommend that anyone actually use those techniques. – Mike D. Jun 07 '14 at 13:19
  • 1
    Precisely. My point is this: evidence from a development server that "things work" is worse than useless. An app with bad or missing `ORDER BY` code may work fine on a small development server and collapse when moved to a production environment. Worse yet, it may collapse a year in to production when some table crosses a size threshold. I've seen both. "Sometimes, but not always", also known as "unpredictable," is a hard concept to grasp in practice. – O. Jones Jun 07 '14 at 13:50