ordering rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ordering rows

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-18 : 08:28:10

hi,

i have following data:


create table tb_customer (customer_id int, order_id varchar (12))
insert into tb_customer (customer_id, order_id) values (123, 2201)
insert into tb_customer (customer_id, order_id) values (123, 2246)
insert into tb_customer (customer_id, order_id) values (123, 2236)
insert into tb_customer (customer_id, order_id) values (123, 1253)
insert into tb_customer (customer_id, order_id) values (120, 1201)
insert into tb_customer (customer_id, order_id) values (127, 2251)
insert into tb_customer (customer_id, order_id) values (127, 2231)
insert into tb_customer (customer_id, order_id) values (122, 2121)

and i want to get the following output:

output:
Nu_order | customer_id | order_id
1 | 123 | 2201
2 | 123 | 2246
3 | 123 | 2236
4 | 123 | 1253
1 | 120 | 1201
1 | 127 | 2251
2 | 127 | 2231
1 | 122 | 2121

any idea?

thank you

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 08:31:54

Are you trying to order everything by the Customer_id? SELECT * FROM tb_customer ORDER BY customer_id
If you want to order them in a way which will not be based on a column(or combination) add another column and specify an order number

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-18 : 08:36:17

jackv: desired output "Nu_order" should be calculated; better said, it should be counted.

i forgot to say i'm using sql 2000 therefore i can't use WITH CTE ...ROW_NUMBER ...over PARTITION functions. :(

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 08:53:29

select (select count(*) from tb_customer as e2
where e2.order_id <= e1.order_id AND e2.customer_id = e1.customer_id) as nu_order,
customer_id,order_id
from tb_customer as e1 order by customer_id,order_id

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 09:04:58

quote:
Originally posted by jackv

select (select count(*) from tb_customer as e2
where e2.order_id <= e1.order_id AND e2.customer_id = e1.customer_id) as nu_order,
customer_id,order_id
from tb_customer as e1 order by customer_id,order_id

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

i dont think this will give OPs o/p as o/p is not given in any particular order of column values.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 09:10:18

The code will return the order of (customer_id ,order_id) per customer_id

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 09:14:37

Alternatively you could try something like this


create table tb_customer (id int identity(1,1),customer_id int, order_id varchar (12))
insert into tb_customer (customer_id, order_id) values (123, 2201)
insert into tb_customer (customer_id, order_id) values (123, 2246)
insert into tb_customer (customer_id, order_id) values (123, 2236)
insert into tb_customer (customer_id, order_id) values (123, 1253)
insert into tb_customer (customer_id, order_id) values (120, 1201)
insert into tb_customer (customer_id, order_id) values (127, 2251)
insert into tb_customer (customer_id, order_id) values (127, 2231)
insert into tb_customer (customer_id, order_id) values (122, 2121)

select t.id-(select count(*) from tb_customer where id<t.id) as Nu_order,
t.customer_id,
t.order_id
from tb_customer t

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-18 : 09:18:06

[code]select RANK() OVER (Partition by Customer_ID Order by Order_ID) as x, customer_id, order_id
from tb_customer[/code]

Note: For SQL 2005 and higher only.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-18 : 09:21:07

Visakh16 : I've run this code and it's returning all Nu_order as "1"

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 09:25:31

quote:
Originally posted by harsh_athalye

select RANK() OVER (Partition by Customer_ID Order by Order_ID) as x, customer_id, order_id
from tb_customer

Note: For SQL 2005 and higher only.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

OP has asked for SQL 2000 compatible solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-18 : 09:30:04

quote:
Originally posted by jackv

Visakh16 : I've run this code and it's returning all Nu_order as "1"

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

sorry. i jumped the gun. try like this:-

select t.id-(select count(*) from tb_customer where customer_id <> t.customer_id and id<t.id) as Nu_order,
t.customer_id,
t.order_id
from tb_customer t

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-02-18 : 17:17:12

visakh16: thank you, ten minutes after i've posted the question, i foun the solution - similar to yours.

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources