0

I am fetching some data from a table. Suppose my query is like this

select ename from emp where job="clerk"

This is returning duplicate ename too but in order in which they have entered.

I modified this to

select distinct ename from emp where job="clerk"

I did this to avoid duplicate values.But ename are coming in alphabetically ascending order. But i want this in the order in which i have entered data as it was coming without using distinct.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Android Killer
  • 18,174
  • 13
  • 67
  • 90
  • Which DB are you using? For example, in Oracle the order is not automatically alphabetically ascending just by using DISTINCT. – beny23 Aug 10 '11 at 12:08

2 Answers2

3

Data in SQL tables have no inherent order. If you want an order, you have to specify it using ORDER BY. If you use DISTINCT, you can only order by columns that you select!

If you have a PK, this would be an approximation:

select distinct id,ename from emp where job="clerk" order by id desc

From an older answer (I knew this would be helpful):

Unless you specify ORDER BY, there are no guarantees about what rows will be returned first.

But in practice, the order will typically match the clustered index order, and will not vary between calls. Don't rely on this behaviour.

Does MySQL's LIMIT keyword guarantee order of returned data?

There is no default sort order. Even if the table has a clustered index, you are not guaranteed to get the results in that order. You must use an order by clause if you want a specific order.

SQL best practice to deal with default sort order

....

Community
  • 1
  • 1
Jacob
  • 41,721
  • 6
  • 79
  • 81
  • You can order by any column on the table, it doesn't have to be in your select clause – iamkrillin Aug 10 '11 at 12:04
  • If you don't use DISTINCT, yes. If you use distinct, how do you want to order by a column you didn't include into your select? There may be multiple "order-by-values" for the same distinct value. – Jacob Aug 10 '11 at 12:05
  • sorry i forgot to say i am using sqlite and when i am using distinct it is coming in alphabetically ascending order like ename having A is coming first,having B coming second and so on. I dont want any order.I want the data in the order in which they exist in database. – Android Killer Aug 10 '11 at 12:06
1

If your table has auto_increment key (id), you can try this:

SELECT ename FROM emp WHERE job="clerk" GROUP BY ename ORDER BY id
vaidas
  • 514
  • 3
  • 9