April 14, 2012

Data ordering issue in MYSQL

Question by Lewis Wheeler

got a small little problem I’m hoping someone can help me with.

I have the following dataset in MYSQL:

SELECT * FROM account;

pk  |  customer
1   |   1
2   |   0
3   |   1

I only need the customer column BUT I need it to be in the same order as above e.g.:

customer
1
0
1

However whenever I try and perform the following command, I get the following:

SELECT customer FROM account

customer
0
1
1

I have already tried the following with no luck:

SET @rownum=0;
SELECT @rownum:=@rownum+1 as rank, customer FROM account

rank |  customer
1    |   0
2    |   1
3    |   1

UPDATE: I forgot to add something important. I can’t rely on ordering by the primary key, mainly because the primary key could be a varchar and not integer in some circumstances.

I need the order in which the data was inserted in the database. When I do the same query returning varchar values it is in the correct order.

Any ideas?

Answer: SQL query no order by question

Answer by Evan Mulawski

SELECT customer FROM account ORDER BY pk ASC

Answer by Starx

You can define the order without selecting that column. So, use this

SELECT `customer` from `account` ORDER BY `pk` ASC

Author: Nabin Nepal (Starx)

Hello, I am Nabin Nepal and you can call me Starx. This is my blog where write about my life and my involvements. I am a Software Developer, A Cyclist and a Realist. I hope you will find my blog interesting. Follow me on Google+

...

Please fill the form - I will response as fast as I can!