Numbering the SQL Query statements

Using the TOAD application for Oracle, I am able to number the SQL query lines for readability. Can the same be done for SQL Server 2005?

Hi,

I think you mean in SQL Server Management Studio.
This is possible by performing the following steps:

In SQL Server Management Studio go to Tools\Options|||

Yes... In Server Management Studio, choose Options from the menu bar.

Expand "Text Editor", Expand "All Languages" and click on General

Under "Display", check the "Line Numbers" box...

sql

Numbering Rows with a Twist

OK,

Here is my challenge.

If I have a query that produces the following

ItemSold_On
A01-10-2004 8:03
A01-11-2004 10:05
A01-12-20041:37
A01-14-20047:16
B01-10-20049:37
B01-12-2004 11:42
B01-13-20049:37

But I need it to produce this instead

ItemSold_On Instance
A01-10-2004 8:031
A01-11-2004 10:052
A01-12-20041:373
A01-14-20047:164
B01-10-20049:371
B01-12-2004 11:422
B01-13-20049:373

So basically I need it to chronologically number the rows, but I need
the count to start over when the item changes.You haven't given us any information about your base table(s). I'll assume
the query result you posted represents an actual table that looks like this:

CREATE TABLE Sometable (item CHAR(1), sold_on DATETIME, PRIMARY KEY
(item,sold_on))

INSERT INTO Sometable VALUES ('A', '2004-01-10T08:03:00')
INSERT INTO Sometable VALUES ('A', '2004-01-11T10:05:00')
INSERT INTO Sometable VALUES ('A', '2004-01-12T01:37:00')
INSERT INTO Sometable VALUES ('A', '2004-01-14T07:16:00')
INSERT INTO Sometable VALUES ('B', '2004-01-10T09:37:00')
INSERT INTO Sometable VALUES ('B', '2004-01-12T11:42:00')
INSERT INTO Sometable VALUES ('B', '2004-01-13T09:37:00')

Here's the query. Depending on your actual data this may not work as
expected (it relies on the combination of (item, sold_on) being unique) or
there may be a better way.

SELECT S1.item, S1.sold_on, COUNT(*) AS instance
FROM Sometable AS S1
JOIN Sometable AS S2
ON S1.item = S2.item
AND S1.sold_on >= S2.sold_on
GROUP BY S1.item, S1.sold_on

Hope this helps.

--
David Portas
----
Please reply only to the newsgroup
--|||> But I need it to produce this instead
> ItemSold_On Instance
> A01-10-2004 8:031
> A01-11-2004 10:052
> A01-12-20041:373
> A01-14-20047:164
> B01-10-20049:371
> B01-12-2004 11:422
> B01-13-20049:373

This won't work until Yukon, but already works in Oracle and DB2...

select item
, sold_on
, row_number() over(partition by item
order by sold_on) as Instance
from your_table;

Christian.|||Thanks David,

I will give it a shot.

Actually the recordset you saw would be from a previous query but I at
least know enough to get your suggestion to work. Even if I have to
create a logical table to do it.

Thanks again,

David Meriwether

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

numbering rows of unordered table?

create table t (colA int, colB char(1), colC int)
insert into t(colB, colC) Values('C', 3)
insert into t(colB, colC) Values('C', 1)
insert into t(colB, colC) Values('C', 4)
insert into t(colB, colC) Values('C', 2)
insert into t(colB, colC) Values('A', 4)
insert into t(colB, colC) Values('A', 1)
insert into t(colB, colC) Values('A', 3)
insert into t(colB, colC) Values('A', 2)
insert into t(colB, colC) Values('B', 2)
insert into t(colB, colC) Values('B', 3)
insert into t(colB, colC) Values('B', 4)
insert into t(colB, colC) Values('B', 1)
so colA of table t contains all nulls right now.
Select * from t
NULL C 3
NULL C 1
NULL C 4
NULL C 2
NULL A 4
NULL A 1
NULL A 3
NULL A 2
NULL B 2
NULL B 3
NULL B 4
NULL B 1
I need to number each row of table t so it looks like this
Select * from t Order By colB, colC
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2
7 B 3
8 B 4
9 C 1
10 C 2
11 C 3
12 C 4
In my actual app Table t already exists with colA = null
and colB and colC as above and thousands of rows. Thus,
to say
Update t set colA = 1 Where colB = 'A' And colC = '1'
Update t set colA = 2 Where colB = 'A' And colC = '2'
Update t set colA = 3 Where colB = 'A' And colC = '3'
...
Update t set colA = 9 Where colB = 'C' And colC = '1'
Update t set colA = 10 Where colB = 'C' And colC = '2'
...
is clearly is not the way to go. I humbly request if
someone could show me how to number the rows with T-sql
the correct way. My problem is that I don't know how to
increment the seed number and how to apply it to the
desired order. I am thinking a while loop, but what flag
to use to stop the loop? How to order the rows?
Thanks,
RonTry,
select
count(*) as colA,
a.colB,
a.colC
from
t as a
inner join
t as b
on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
group by
a.colB,
a.colC
order by
1
go
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Ron" wrote:

> create table t (colA int, colB char(1), colC int)
> insert into t(colB, colC) Values('C', 3)
> insert into t(colB, colC) Values('C', 1)
> insert into t(colB, colC) Values('C', 4)
> insert into t(colB, colC) Values('C', 2)
> insert into t(colB, colC) Values('A', 4)
> insert into t(colB, colC) Values('A', 1)
> insert into t(colB, colC) Values('A', 3)
> insert into t(colB, colC) Values('A', 2)
> insert into t(colB, colC) Values('B', 2)
> insert into t(colB, colC) Values('B', 3)
> insert into t(colB, colC) Values('B', 4)
> insert into t(colB, colC) Values('B', 1)
> so colA of table t contains all nulls right now.
> Select * from t
> NULL C 3
> NULL C 1
> NULL C 4
> NULL C 2
> NULL A 4
> NULL A 1
> NULL A 3
> NULL A 2
> NULL B 2
> NULL B 3
> NULL B 4
> NULL B 1
> I need to number each row of table t so it looks like this
> Select * from t Order By colB, colC
> 1 A 1
> 2 A 2
> 3 A 3
> 4 A 4
> 5 B 1
> 6 B 2
> 7 B 3
> 8 B 4
> 9 C 1
> 10 C 2
> 11 C 3
> 12 C 4
> In my actual app Table t already exists with colA = null
> and colB and colC as above and thousands of rows. Thus,
> to say
> Update t set colA = 1 Where colB = 'A' And colC = '1'
> Update t set colA = 2 Where colB = 'A' And colC = '2'
> Update t set colA = 3 Where colB = 'A' And colC = '3'
> ...
> Update t set colA = 9 Where colB = 'C' And colC = '1'
> Update t set colA = 10 Where colB = 'C' And colC = '2'
> ...
> is clearly is not the way to go. I humbly request if
> someone could show me how to number the rows with T-sql
> the correct way. My problem is that I don't know how to
> increment the seed number and how to apply it to the
> desired order. I am thinking a while loop, but what flag
> to use to stop the loop? How to order the rows?
> Thanks,
> Ron
>|||here is the update.
update
t
set
colA = (select count(*) from t as a where t.colB + ltrim(t.colC) >= a.colB
+ ltrim(a.colC))
go
AMB
"Alejandro Mesa" wrote:
> Try,
> select
> count(*) as colA,
> a.colB,
> a.colC
> from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
> group by
> a.colB,
> a.colC
> order by
> 1
> go
> How to dynamically number rows in a SELECT Statement
> http://support.microsoft.com/defaul...kb;en-us;186133
>
> AMB
>
> "Ron" wrote:
>|||Thanks very much for your reply. I guess the trick was in
the self join. I took this one step further and performed
an update (as I need to hardcode these numbers):
update t set t.colA = t2.colA
From t Join
(select count(*) as colA, a.colB, a.colC from t as a inner
join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
(b.colC) group by a.colB, a.colC) t2
on t.colB = t2.colB and t.colC = t2.colC
Question: someone advised me that using joins in an
update statement is not correct. But this Update
statement accomplished what I needed. Any comments
appreciated.
Thanks again for your help.
Ron

>--Original Message--
>Try,
>select
> count(*) as colA,
> a.colB,
> a.colC
>from
> t as a
> inner join
> t as b
> on a.colB + ltrim(a.colC) >= b.colB + ltrim(b.colC)
>group by
> a.colB,
> a.colC
>order by
> 1
>go
>How to dynamically number rows in a SELECT Statement
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;186133
>
>AMB|||Read my last post.
AMB
"Ron" wrote:

> Thanks very much for your reply. I guess the trick was in
> the self join. I took this one step further and performed
> an update (as I need to hardcode these numbers):
> update t set t.colA = t2.colA
> From t Join
> (select count(*) as colA, a.colB, a.colC from t as a inner
> join t as b on a.colB + ltrim(a.colC) >= b.colB + ltrim
> (b.colC) group by a.colB, a.colC) t2
> on t.colB = t2.colB and t.colC = t2.colC
> Question: someone advised me that using joins in an
> update statement is not correct. But this Update
> statement accomplished what I needed. Any comments
> appreciated.
> Thanks again for your help.
> Ron
>
> us;186133
>|||Thanks again for this correction.

>--Original Message--
>here is the update.
>update
> t
>set
> colA = (select count(*) from t as a where t.colB +
ltrim(t.colC) >= a.colB
>+ ltrim(a.colC))
>go
>
>AMB
>"Alejandro Mesa" wrote:
>
us;186133
this
null
Thus,
sql
to
flag
>.
>

numbering rows

How do i number the rows in the table? My data does not have any unique
values if that helpsThere is a RowNumber(<scope>) function, where <scope> is a string that
identifies a data region, dataset or grouping if you need that context. For
just a simple running row total, use RowNumber(Nothing). You can also use
it for visual effects, and the most frequent example of this is to create
"green bar" reports by setting the background colour of a table row with the
expression:
=iif(RowNumber(Nothing) Mod 2, "Green", "White")
Cheers, Mark
"Marvin" <Marvin@.discussions.microsoft.com> wrote in message
news:25E67CAB-76C0-450A-B4DE-87788DD19E80@.microsoft.com...
> How do i number the rows in the table? My data does not have any unique
> values if that helps

Numbering Query Results

Is there a way to have a column in a query result that is an "autonumber"?
Say I have 10 records returned, I want them to be numbered 1 - 10. I'm sure
there's a way using a stored procedure or something, I just can't think of a
way.
Thanks in advance.
Chuck Foster
Programmer Analyst
Eclipsys Corporation - St. Vincent Health SystemSee:
http://support.microsoft.com/defaul...b;EN-US;q186133
Anith|||How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"chuckdfoster" wrote:

> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm su
re
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>
>|||Thanks...
"chuckdfoster" <chuckdfoster@.hotmail.com> wrote in message
news:O7e$7waSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Is there a way to have a column in a query result that is an "autonumber"?
> Say I have 10 records returned, I want them to be numbered 1 - 10. I'm
sure
> there's a way using a stored procedure or something, I just can't think of
a
> way.
> Thanks in advance.
> --
> Chuck Foster
> Programmer Analyst
> Eclipsys Corporation - St. Vincent Health System
>

Numbering of records in a Group

I just started learnig CR.
1) I want to know how to serially number records which form part of a particular group. "Group Number" gives the group a num and "Record Number" gives a global num. I want each list in a group should have a distinct set of numbers.
Sorry if the question is very silly, I enquired couple of people but no help!

2) I also want to know how do we display a CR from VB and print it to deskjet printer.

Version info:
CR 9 & CR 4.2
VB6Write your own formulas to count them.
e.g. in group header
whileprintingrecords;
numbervar x := 0;

in detail:
whileprintingrecords;
numbervar x := x + 1;|||Like I said in the first instance Iam a novice in CR (that explains my username). Though you have stated to add some variables in group header and increment in detail section, I donot see any "code window" as in VB where I can add the code suggested. I would be happy if you could take some pains to explain me that as well.

Also let me know how to connect, open & print CR9 report through VB6, the database being Access 2003. The connection needs to be dynamic i.e. I should be able to manipulate the connxn programatically through VB6.|||Nobody has responded. Can moderators help me.|||right click on formula fields in field explorer, select new then code window for the formula will be opened. u need to do this in crystal desinger not in vb6 form designer.

Numbering Lines (Ranking)

I have a report that show all of are salesmen sorted by the number of sales they have. What I have been asked to do is to give them an actual ranking (Number 1, Number 2 etc...). Being fairly new to SQL I am not sure how to do this so here I am.

Thank you for any help that anyone can give me.

You can use RowNumber(Nothing). Check out the docs for more info.

|||Thank you for the help.

Numbering in SQL

I have table with 10-20 rows with field P6 which is empty. I want to
update numbers to P6 starting 1 and increasing by 1. I suppose it is
done by triggers but I don't know how to do that. Help :-)is this as 1 off or every time you INSERT a record?

--
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)|||Make the column a identity value, this way it is incremented with every
insert:

int IDENTITY (1, 1)|||lemes_m@.yahoo.com (lemes_m@.yahoo.com) writes:
> I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

Without further knowledge about the table it is difficult to give
advice. And if the rest of the data is not unique, it's getting sort
of ugly.

For a one-off you could do:

DECLARE @.i int
SELECT @.i = 1
-- SET ROWCOUNT 1 Use this on SQL 2000.
WHILE EXISTS (SELECT * FROM tbl WHERE P6 IS NULL)
BEGIN
UPDATE /* TOP(1) */ tbl -- Remove comment for SQL 2005.
SET P6 = @.i

SELECT @.i = @.i + 1
END
-- SET ROWCOUNT 0 again, for SQL 2000.

But I would not like to see this code in a trigger.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> have table with 10-20 rows with field [sic] P6 which is empty [sic]. <<

You have NEVER written SQL before, have you? Columsn are not fields
and we have NULLs, not the empty spreadsheet cells you assume. TOTALLY
WRONG MINDSET!

>> I want to update numbers to P6 starting 1 and increasing by 1. <<

That is a SEQUENTAL MAGNETIC TAPE FILE and you are tryignto write
1950's code in SQL! It has northing whatsoever to do with RDBMS.
Tables have no ordering by definition. This is soooooo wrong ...|||Geeesh!!! You can insert a set at a time, so adding one to a previous
value makes no sense. Doesn't anyone go to RDBMS classes any more?|||>> Make the column a identity value, this way it is incremented with every insert: <<

Always go for the proprietary and most non-relational kludge? LET'S
FINBD OUT WHAT HE IS REALLY TRYIGN TO DO BEFORE WE POST ANYTHING ELSE.

Okay, lemes_m@.yahoo.com , why do you want to destroy the relational
model? What is your business goal?|||Hi Lemes,

Check out http://blogs.msdn.com/sqlcat/archiv.../10/572848.aspx it has
good examples of how to do this.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

<lemes_m@.yahoo.com> wrote in message
news:1148037597.406952.273140@.j33g2000cwa.googlegr oups.com...
>I have table with 10-20 rows with field P6 which is empty. I want to
> update numbers to P6 starting 1 and increasing by 1. I suppose it is
> done by triggers but I don't know how to do that. Help :-)

numbering in group

I want to add numbering in group in the reports.(number each row in the detail)

when I worked with access it was possible.

can I do it in the report services?

thanks!Ok I find it:

=rownumber("MyGropName")

thankssql

Numbering groups of rows?

create table t (colA int, colB char(1), colC int)
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('C')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('A')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
insert into t(colB) Values('B')
update t set colc =
(select count(*) from t as a where t.colb >= a.colb)
yields
NULL C 12
NULL C 12
NULL C 12
NULL C 12
NULL A 4
NULL A 4
NULL A 4
NULL A 4
NULL B 8
NULL B 8
NULL B 8
NULL B 8
how can I make it yield
NULL C 3
NULL C 3
NULL C 3
NULL C 3
NULL A 1
NULL A 1
NULL A 1
NULL A 1
NULL B 2
NULL B 2
NULL B 2
NULL B 2
Thanks,
Ronupdate t set colc =
(select count(DISTINCT a.colb) from t as a where t.colb >= a.colb)
Jacco Schalkwijk
SQL Server MVP
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
> create table t (colA int, colB char(1), colC int)
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('C')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('A')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> insert into t(colB) Values('B')
> update t set colc =
> (select count(*) from t as a where t.colb >= a.colb)
> yields
> NULL C 12
> NULL C 12
> NULL C 12
> NULL C 12
> NULL A 4
> NULL A 4
> NULL A 4
> NULL A 4
> NULL B 8
> NULL B 8
> NULL B 8
> NULL B 8
> how can I make it yield
> NULL C 3
> NULL C 3
> NULL C 3
> NULL C 3
> NULL A 1
> NULL A 1
> NULL A 1
> NULL A 1
> NULL B 2
> NULL B 2
> NULL B 2
> NULL B 2
> Thanks,
> Ron
>|||On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:

>update t set colc =
>(select count(*) from t as a where t.colb >= a.colb)
>yields
(snip)
>how can I make it yield
(snip)
Hi Ron,
Better not to store this information at all - you'll find yourself
constantly fighting to keep the rankingf column current after each
modification to the underlying data. It's better to drop the column from
the table and create a view to calculate it.
If you MUST do it in an update, try
update t set colc =
(select count(distinct a.colb) from t as a where t.colb >= a.colb)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. That worked well. But I also tried
using this in a select statement as follows for ranking
the values A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
I ask this because I am trying to understand the sql logic
to achieve these results. Hopefully, after I do enough of
these kinds of queries I will get the idea how they work.
May I ask how I could achieve the ranking from result3?
Thanks again,
Ron

>--Original Message--
> update t set colc =
>(select count(DISTINCT a.colb) from t as a where t.colb
>= a.colb)
>
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Ron" <anonymous@.discussions.microsoft.com> wrote in
message
>news:01b401c50fb9$5a806ee0$a501280a@.phx.gbl...
>
>.
>|||Ron,
Try count(distinct b.colb) instead of count(distinct a.colb). I suspect
that's what you had in mind.
Steve Kass
Drew University
Ron wrote:
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql logic
>to achieve these results. Hopefully, after I do enough of
>these kinds of queries I will get the idea how they work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>
>message
>|||On Thu, 10 Feb 2005 14:21:58 -0800, Ron wrote:

>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
Hi Ron,
Try this one instead:
select a.colB, count(distinct b.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
(Note: only one letter weas changed!!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks very much. I am also trying to figure out how to
rank A, B, C
select a.colB, count(distinct a.colb) as colC
from t as a inner join t as b
on a.colB >= b.colB
group by a.colB
yielded this ranking
A 1
B 1
C 1
without the Distinct keyword I get this ranking
A 16
B 32
C 48
But I would like to get a ranking as follows
A 1
B 2
C 3
May I ask how I could achieve the ranking from result3?
This way, as you say, I don't really store the ranks, just
retrieve them dynamically.
Thanks again,
Ron

>--Original Message--
>On Thu, 10 Feb 2005 13:42:01 -0800, Ron wrote:
>
>(snip)
>(snip)
>Hi Ron,
>Better not to store this information at all - you'll find
yourself
>constantly fighting to keep the rankingf column current
after each
>modification to the underlying data. It's better to drop
the column from
>the table and create a view to calculate it.
>If you MUST do it in an update, try
>update t set colc =
>(select count(distinct a.colb) from t as a where t.colb
>= a.colb)
>Best, Hugo
>--
>(Remove _NO_ and _SPAM_ to get my e-mail address)
>.
>|||Thanks all. I am sort of starting to get the idea. But I
wonder if I could belabor this thing one more notch:
Instead of using distinct is it possible to plant a group
by query in there? Pseudocode here:
select a.colB, count(select a.colb from a group by a.colb)
as colC from t as a inner join t as b
on a.colB >= b.colB group by a.colB
Again, I just ask because I don't really know all the
rules for t-sql, let alone the tricks. I am guessing that
t-sql does not allow Selects inside of Count(..)
Thanks again,
Ron

>--Original Message--
>Thanks very much. That worked well. But I also tried
>using this in a select statement as follows for ranking
>the values A, B, C
>select a.colB, count(distinct a.colb) as colC
>from t as a inner join t as b
>on a.colB >= b.colB
>group by a.colB
>yielded this ranking
>A 1
>B 1
>C 1
>without the Distinct keyword I get this ranking
>A 16
>B 32
>C 48
>But I would like to get a ranking as follows
>A 1
>B 2
>C 3
>I ask this because I am trying to understand the sql
logic
>to achieve these results. Hopefully, after I do enough
of
>these kinds of queries I will get the idea how they
work.
>May I ask how I could achieve the ranking from result3?
>Thanks again,
>Ron
>
>message
>.
>|||On Thu, 10 Feb 2005 14:45:11 -0800, Ron wrote:

>Thanks all. I am sort of starting to get the idea. But I
>wonder if I could belabor this thing one more notch:
>Instead of using distinct is it possible to plant a group
>by query in there? Pseudocode here:
>select a.colB, count(select a.colb from a group by a.colb)
>as colC from t as a inner join t as b
>on a.colB >= b.colB group by a.colB
Hi Ron,
This code won't work. I'm sure there is some way to do this with a group
by in the subquery, but it's not trivial and it'll be more complex than
the version with DISTINCT that I suggested.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Instead of using distinct is it possible to plant a group by query
in there? <<
This will use a GROUP BY and get you a bit more information in the VIEW
aggregate functions. I am not sure that there is any advantage. .
CREATE TABLE Foobar (letter CHAR(1) NOT NULL);
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('C');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('A');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
INSERT INTO Foobar (letter) VALUES('B');
CREATE VIEW FoobarReport (letter, occurs, place)
AS
SELECT F1.letter, COUNT(*),
(SELECT COUNT (DISTINCT F2.letter)
FROM Foobar AS F2
WHERE F2.letter <= F1.letter)
FROM Foobar AS F1
GROUP BY F1.letter;

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?
Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table.
Example: select <...>, state from tbl_states (<...> is what I am asking for)
Col1 Col2
--
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
--
David Portas
SQL Server MVP
--|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

Numbering each row in my resultset

In my query I want to succesively number each record I retrieve from a table
.
Example: select <...>, state from tbl_states (<...> is what I am asking for
)
Col1 Col2
--
1 Ohio
2 New York
3 California
4 Texas
I have seen some solutions using count(*), group by and joins to achieve
this. However I have images that I retrieve in columns 2 (instead of the
states) and group by is not compliant with image datatype.
I think Oracle has something called rownum that does what I want, but I
don't know how to do this with SQL Server.
Anybody knows?Why not add the row number client-side? That can work out much more
efficient than any of the potential SQL solutions. Here's one SQL
alternative:
SELECT
(SELECT COUNT(*)
FROM YourTable
WHERE key_col <= T.key_col) AS col1,
col2
FROM YourTable AS T
David Portas
SQL Server MVP
--|||Why make the query treat each row individually? The client has to do that
anyway, so it is a much more appropriate place to keep a running count.
http://www.aspfaq.com/
(Reverse address to reply.)
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:D2BEB8E4-C710-4027-A1EF-1D8166A6DB10@.microsoft.com...
> In my query I want to succesively number each record I retrieve from a
table.
> Example: select <...>, state from tbl_states (<...> is what I am asking
for)
> Col1 Col2
> --
> 1 Ohio
> 2 New York
> 3 California
> 4 Texas
> I have seen some solutions using count(*), group by and joins to achieve
> this. However I have images that I retrieve in columns 2 (instead of the
> states) and group by is not compliant with image datatype.
> I think Oracle has something called rownum that does what I want, but I
> don't know how to do this with SQL Server.
> Anybody knows?
>

numbering each row

I'm sure this has been asked a bunch of times, but I can't find samples that
apply to many groups and columns. I also found ROW_NUMBER() function for SQL
2005. Unfortunately, I have SQL 2000. I realize this should be done in the
display of data but I have to get it done in a table. I believe RANK is what
I need, but didn't get it working. It would return a 1 for each row. I've
also considered using temp tables and an ID column, but that involves a LOT
of temp tables.
I need to add a row numbering column to the below results.
It must be grouped on the s.OrderNo field.
How do I get that to work?
SELECT s.OrderNo, t.Title, t.Artist, t.Location,
t.SortKey, t.MP3Files, t.OrderNo AS Disc_ID
FROM ItemStock s INNER JOIN
ItemPackCompilations p ON s.OrderNo = p.PackOrderNo INNER JOIN
ItemTitles t ON p.IncludeOrderNo = t.OrderNo
WHERE (s.Packed = 'Comp')
ORDER BY s.OrderNo, t.Title
thanks!> I'm sure this has been asked a bunch of times, but I can't find samples
> that apply to many groups and columns. I also found ROW_NUMBER() function
> for SQL 2005. Unfortunately, I have SQL 2000.
http://www.aspfaq.com/2427

> I realize this should be done in the display of data but I have to get it
> done in a table.
Why? Does a business requirement somehow state that the data must be
displayed directly from a table? Why?

> I believe RANK is what I need, but didn't get it working.
Well, this should fail for the same reason ROW_NUMBER() won't work for you.
RANK is SQL 2005+.

> SELECT s.OrderNo, t.Title, t.Artist, t.Location,
> t.SortKey, t.MP3Files, t.OrderNo AS Disc_ID
> FROM ItemStock s INNER JOIN
> ItemPackCompilations p ON s.OrderNo = p.PackOrderNo INNER JOIN
> ItemTitles t ON p.IncludeOrderNo = t.OrderNo
> WHERE (s.Packed = 'Comp')
> ORDER BY s.OrderNo, t.Title
I don't know what this is. Please see http://www.aspfaq.com/5006sql

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.
How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.
|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>
|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
row[vbcol=seagreen]

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
.. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
> > How will you determine what the order should be? That is, should the
row
> > with AgencyID = 10 have a StatId of 109?
> >
> >
> > "Jack" <Jack@.discussions.microsoft.com> wrote in message
> > news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> > > Hi,
> > > I have a table as follows:
> > >
> > > StatId AgencyID Value
> > > 1 10
> > > 2 47
> > > 3 38
> > > 4 59
> > > 5 60
> > > .. ..
> > > All the fields in the StatId field is blank. However,
> > > I have to fill up the field in statId starting from
> > > 109 with increment of 1 for each row. Altogether I have
> > > about 10,000 row in the above agency table.
> > >
> > > Any help as to how to proceed programmatically is highly
> > > appreciated. Thanks in advance.
> >
> >
> >

Numbering column with a start number of 109

Hi,
I have a table as follows:
StatId AgencyID Value
1 10
2 47
3 38
4 59
5 60
. ..
All the fields in the StatId field is blank. However,
I have to fill up the field in statId starting from
109 with increment of 1 for each row. Altogether I have
about 10,000 row in the above agency table.
Any help as to how to proceed programmatically is highly
appreciated. Thanks in advance.How will you determine what the order should be? That is, should the row
with AgencyID = 10 have a StatId of 109?
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
> Hi,
> I have a table as follows:
> StatId AgencyID Value
> 1 10
> 2 47
> 3 38
> 4 59
> 5 60
> .. ..
> All the fields in the StatId field is blank. However,
> I have to fill up the field in statId starting from
> 109 with increment of 1 for each row. Altogether I have
> about 10,000 row in the above agency table.
> Any help as to how to proceed programmatically is highly
> appreciated. Thanks in advance.|||That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
"Adam Machanic" wrote:

> How will you determine what the order should be? That is, should the row
> with AgencyID = 10 have a StatId of 109?
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:F4B0FBC9-1CFF-4E72-A572-1AA9273C5D29@.microsoft.com...
>
>|||What's the logic then? How will you programatically number these rows?
You'll probably have to write a loop to manually update the rows, one by
one, based on whatever logic you're ordering them by.
Or, you could try creating a new table with StatId INT IDENTITY(109, 1),
then insert the entire batch at once using INSERT SELECT, with an ORDER BY,
but there is no guarantee that the rows will show up in the right order. So
although you could try that, it may not work the way you want.
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:65435A6C-DB6B-4828-A4F4-754005BB31E7@.microsoft.com...[vbcol=seagreen]
> That's correct. Row with Agencyid = 10 will have a StatID of 109. Thanks.
> "Adam Machanic" wrote:
>
row[vbcol=seagreen]

numbering a query

Is there a quick way to number a query?

ie I want to number the records returned by a recordset consecutively.

This seems like it should be simple but I haven't figured out how to do it
yet.

Any help is appreciated!

TIA

Carter"me" <me@.work.com> wrote in message
news:10hn03h10dtja5a@.corp.supernews.com...
> Is there a quick way to number a query?
> ie I want to number the records returned by a recordset consecutively.
> This seems like it should be simple but I haven't figured out how to do it
> yet.
> Any help is appreciated!
> TIA
> Carter

http://www.aspfaq.com/show.asp?id=2427

Simon|||Thanks much I'll take a look

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:411b9a40$1_3@.news.bluewin.ch...
> "me" <me@.work.com> wrote in message
> news:10hn03h10dtja5a@.corp.supernews.com...
> > Is there a quick way to number a query?
> > ie I want to number the records returned by a recordset consecutively.
> > This seems like it should be simple but I haven't figured out how to do
it
> > yet.
> > Any help is appreciated!
> > TIA
> > Carter
> http://www.aspfaq.com/show.asp?id=2427
> Simon

Numbering

I have a table with Employees, and the month and year they started. I need to number them in order of the month and year they started. Example: July 2001 is employee 1 and August 2001 is employee 2. However there may be multiple employees in a given month and year. It seems like this should be a simple task but I can not figure it out. I have a query that puts them in the right order but not sure how to update the ID with the correct number. I'm using Microsoft Access 2002.

Thanks

TheAceManPlease post that query. Also, what are the data types for month and year?sql