Creating a sequential number in a column.

Hi,
I'd like to generate a column in a query which shows the row number
chronologically (Num) as:
Cust_ID Sales Date Num
526 12.350 12/5/2007 1
632 11.520 5/5/2007 2
123 10.899 6/6/2007 3
.. ... ... 4
Howto achieve it?
TIA
Ana
That doesn't look chronological to me. Why is 12/5/2007 1 and 5/5/2007 2?
Can you apply the same numbers in some logical way *without* visually
inspecting the arbitrary order of rows that come back from SELECT * FROM
table ?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Hi,
Sorry, I didn't explain myself well. The result of a query is a ranking
based on customers' sales.
The fields from a table are:
Cust_ID
Sales
Date (European format)
The query generates the following results:
Cust_ID Sales Date
526 12.350 12/5/2007
632 11.520 5/5/2007
123 10.899 6/6/2007
Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
Customer ID 632 generated 11.520 euros so should be 2.
And Cust. ID 123 should be 3. and etc.
So I was wondering if a column can be generated in a query which would label
the ranking from 1 to wherever ends the query. Meaning, if I have 10 rows so
will be till 10.
Hope I have been a bit clearer.
Thank you much for your prompt response.
Ana
"Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
> Hi,
> I'd like to generate a column in a query which shows the row number
> chronologically (Num) as:
>
> Cust_ID Sales Date Num
> 526 12.350 12/5/2007 1
> 632 11.520 5/5/2007 2
> 123 10.899 6/6/2007 3
> . ... ... 4
>
> Howto achieve it?
> TIA
> Ana
>
|||Customers sell things? Okay, so what is the key on this table? Is it
Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
rows:
526 12.350 12/5/2007
526 12.250 6/6/2007
525 12.300 12/5/2007
525 12.400 12/4/2007
What should the result be?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Ana" <ananospam@.yahoo.es> wrote in message
news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
> Hi,
> Sorry, I didn't explain myself well. The result of a query is a ranking
> based on customers' sales.
> The fields from a table are:
> Cust_ID
> Sales
> Date (European format)
> The query generates the following results:
> Cust_ID Sales Date
> 526 12.350 12/5/2007
> 632 11.520 5/5/2007
> 123 10.899 6/6/2007
>
> Customer ID 526 generated 12.350 euros so should be labelled as Number 1.
> Customer ID 632 generated 11.520 euros so should be 2.
> And Cust. ID 123 should be 3. and etc.
> So I was wondering if a column can be generated in a query which would
> label the ranking from 1 to wherever ends the query. Meaning, if I have 10
> rows so will be till 10.
> Hope I have been a bit clearer.
> Thank you much for your prompt response.
> Ana
>
> "Ana" <ananospam@.yahoo.es> escribi en el mensaje de noticias
> news:7DE04B91-B198-4EEA-9B2C-91F5685C91AF@.microsoft.com...
>
|||Ha, ha, ha. Well it's rather odd but yes, customers do sell because they
convert themselves into agents under some conditions. But it's a side
matter.
In my query I use the SUM(CASE .WHEN.) to sum their sells within a specific
period (let's forget the dates) which generates a single line per customer
therefore the results could be as:
526 12.350
525 12.400
Where Cust_ID is PK, sales is numeric and date is dates. Meaning that cust
526 has generated 12.350 euros vs. cust 525 who generated 12.400 euros.
Now in my ranking I want to label cust 525 as a 1 and cust 526 as a 2 and so
on.
Thank you, and sorry for the confusion.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> escribi en el
mensaje de noticias news:u$iERPGqHHA.3892@.TK2MSFTNGP05.phx.gbl...
> Customers sell things? Okay, so what is the key on this table? Is it
> Cust_ID? Or Cust_ID and date? Or no key at all? If I have these three
> rows:
> 526 12.350 12/5/2007
> 526 12.250 6/6/2007
> 525 12.300 12/5/2007
> 525 12.400 12/4/2007
> What should the result be?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
> "Ana" <ananospam@.yahoo.es> wrote in message
> news:eVfKZ2FqHHA.3660@.TK2MSFTNGP04.phx.gbl...
>