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?
>

Number of rows depending on a column value

Hi all,

I have a table with artikels and count, sample:

Art Count

12A 3

54G 2

54A 4

I would like to query this table and for each 'count' retrieve one row:

query result:

Art Count

12A 3

12A 3

12A 3

54G 2

54G 2

54A 4

54A 4

54A 4

54A 4

Is this possible?

Thanks, Perry

Certainly one way to do this would be to join with a table of numbers. Give a look to this article about a table of numbers here. In the meantime, I will see about getting you an example. Also, if this info is meant for a front end application, you might consider doing this work in the application instead of the database. Here is an example:

Code Snippet

declare @.artikels table
( Art varchar(5),
[Count] integer
)
insert into @.Artikels
select '12A', 3 union all
select '54G', 2 union all
select '54A', 4

select Art, [count]
from @.artikels
join numbers
on [count] >= n
and n <= 10 -- setting an arbitrary upper bound
order by Art

/*
Art count
-- --
12A 3
12A 3
12A 3
54A 4
54A 4
54A 4
54A 4
54G 2
54G 2
*/

|||

Thanks

This can do the job, but not the (easy) way i was looking for. I need a view to feed a report writer (data dynamics active reports) to print barcodes, for each product 1 barcode.

Now i will query the view and copy records in the resultset as many as needed, then feed this dataset to the report writer

Thanks

Perry

I

Number of rows depending on a column value

Hi all,

I have a table with artikels and count, sample:

Art Count

12A 3

54G 2

54A 4

I would like to query this table and for each 'count' retrieve one row:

query result:

Art Count

12A 3

12A 3

12A 3

54G 2

54G 2

54A 4

54A 4

54A 4

54A 4

Is this possible?

Thanks, Perry

Certainly one way to do this would be to join with a table of numbers. Give a look to this article about a table of numbers here. In the meantime, I will see about getting you an example. Also, if this info is meant for a front end application, you might consider doing this work in the application instead of the database. Here is an example:

Code Snippet

declare @.artikels table
( Art varchar(5),
[Count] integer
)
insert into @.Artikels
select '12A', 3 union all
select '54G', 2 union all
select '54A', 4

select Art, [count]
from @.artikels
join numbers
on [count] >= n
and n <= 10 -- setting an arbitrary upper bound
order by Art

/*
Art count
-- --
12A 3
12A 3
12A 3
54A 4
54A 4
54A 4
54A 4
54G 2
54G 2
*/

|||

Thanks

This can do the job, but not the (easy) way i was looking for. I need a view to feed a report writer (data dynamics active reports) to print barcodes, for each product 1 barcode.

Now i will query the view and copy records in the resultset as many as needed, then feed this dataset to the report writer

Thanks

Perry

I