SQL Query (SQL Server)

I have the following records

CarNumber     createtime
B509          2015-02-26 11:54:41.1620000
B509          2015-02-26 11:55:41.1620000
D600          2015-02-25 11:55:41.1620000
D600          2015-02-24 11:53:41.1620000
D708          2015-02-28 11:53:41.1620000

I want the following records displayed as per createtime (ASC) and per carNumber (Group wise) . Seq_No should be incremental and all the records should be in Order of CarNumber



CarNumber       seq_no
D600                   1
D600                   2
B509                   3
B509                   4
D600                   5
D708                   6
learner0824Asked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
You can use this...

select carnumber,
row_number() over (order by carnumber,createtime) Seq_No
from table1

Open in new window


or

select carnumber,
rank() over (order by carnumber,createtime) Seq_No
from table1

Open in new window


Saurabh...
0
 
Saurabh Singh TeotiaCommented:
You can use the following to do what you are looking for...

select carnumber,
row_number() over (partion by carnumber order by createtime) Seq_No
from table1

Open in new window


or

select carnumber,
rank() over (partion by carnumber order by createtime) Seq_No
from table1

Open in new window


Saurabh..
0
 
learner0824Author Commented:
Thanks. I tried what you said
select carnumber,
row_number() over (partion by carnumber order by createtime) Seq_No
from table1

But the sequence number increements by group like
D600                   1
 D600                   2
 B509                   1
 B509                   2
 D600                   1
 D708                   1

I want it incrmeental all across the rows like  1,2,3,4,5,6  so on
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Saurabh Singh TeotiaCommented:
I thought you said incremental but basis of car number..However if you just want incremental basis of time then you can simply use...

select carnumber,
row_number() over (order by createtime) Seq_No
from table1

Open in new window


or

select carnumber,
rank() over (order by createtime) Seq_No
from table1

Open in new window

0
 
learner0824Author Commented:
Thanks. In this case all the numbers get scattered . ie CarNumbers . They are disaplyed as

  D600                   1
  B509                   2
  D600                   3
  D708                   4
  B509                   5

As you can see D600 is not displayed in Order and even D509 as it goes by createtime.  But I would like to have it sorted in order by carnumber and increment sequence number  like

CarNumber       seq_no
 D600                   1
 D600                   2
 B509                   3
 B509                   4
 D600                   5
 D708                   6
0
 
PortletPaulfreelancerCommented:
perhaps what you are asking for isn't possible......

you have supplied 5 rows with 2 columns of data
CarNumber     createtime
B509          2015-02-26 11:54:41.1620000
B509          2015-02-26 11:55:41.1620000
D600          2015-02-25 11:55:41.1620000
D600          2015-02-24 11:53:41.1620000
D708          2015-02-28 11:53:41.1620000

If order is based on createtime (ASC)

CarNumber     createtime
D600          2015-02-24 11:53:41.1620000
D708          2015-02-28 11:53:41.1620000
B509          2015-02-26 11:54:41.1620000
B509          2015-02-26 11:55:41.1620000
D600          2015-02-25 11:55:41.1620000

or createtime (DESC)

CarNumber     createtime
D600          2015-02-25 11:55:41.1620000
B509          2015-02-26 11:54:41.1620000
B509          2015-02-26 11:55:41.1620000
D600          2015-02-24 11:53:41.1620000
D708          2015-02-28 11:53:41.1620000

if the order is by BOTH CarNumber then createtime (DESC), then car numbers starting with B will be before car numbers starting with D

CarNumber     createtime
B509          2015-02-26 11:54:41.1620000
B509          2015-02-26 11:55:41.1620000
D600          2015-02-25 11:55:41.1620000
D600          2015-02-24 11:53:41.1620000
D708          2015-02-28 11:53:41.1620000

Perhaps you could describe how you arrive at the desired order AND display ALL other columns that are relevant to producing that order

CarNumber       seq_no
 D600                   1            is this the latest time? within D600?
 D600                   2
 B509                   3            is this the latest time? within B509? whay is B AFTER D
 B509                   4
 D600                   5           <<<<<<<< how is this possible there are only 2 D600 data rows

 D708                   6           if B is AFTER D how do we get back to D?
0
 
PortletPaulfreelancerCommented:
a suggestion try this:

select carnumber,
row_number() over (order by NULL) Seq_No
from table1

Open in new window


or

select carnumber,
row_number() over (order by (select 1)) Seq_No
from table1

Open in new window

0
 
learner0824Author Commented:
Thanks. Posting similar question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.