Line Numbers in SQL

Is there a function I can put into my select clause that tell me what the
line number of the row is?
i.e. in Oracle you can do something like (Been a year since I've used
Oracle)
select rownum, Field1, Field2
from blah
Any help is appreciated. Thanks.No, you could do something with a temp table and IDENTITY. I believe Yukon
has the rownumber function. What do you need it for?
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uTDWhna8DHA.1052@.TK2MSFTNGP12.phx.gbl...
> Is there a function I can put into my select clause that tell me what the
> line number of the row is?
> i.e. in Oracle you can do something like (Been a year since I've used
> Oracle)
> select rownum, Field1, Field2
> from blah
> Any help is appreciated. Thanks.
>|||It's the only way I can think of to alternate row colors in reporting
services...
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:%233NMuic8DHA.1548@.tk2msftngp13.phx.gbl...
> No, you could do something with a temp table and IDENTITY. I believe Yukon
> has the rownumber function. What do you need it for?
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uTDWhna8DHA.1052@.TK2MSFTNGP12.phx.gbl...
the
>|||you should have a look at the subject "row style in matrix" in
microsoft.public.sqlserver.reportingsvcs
Jean-Philippe.
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> a crit dans le
message de news:eDztD6e8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> It's the only way I can think of to alternate row colors in reporting
> services...
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:%233NMuic8DHA.1548@.tk2msftngp13.phx.gbl...
Yukon
message
> the
>|||I don't seem to have a message with that title and the newsgroup is not in
Google groups yet...
"Jean Philippe Vankemmel" <vankemjp@.ifrance.com> wrote in message
news:%23zh0zZG9DHA.452@.TK2MSFTNGP11.phx.gbl...
> you should have a look at the subject "row style in matrix" in
> microsoft.public.sqlserver.reportingsvcs
> Jean-Philippe.
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> a crit dans le
> message de news:eDztD6e8DHA.2404@.TK2MSFTNGP12.phx.gbl...
> Yukon
> message
what
used
>|||See here - http://www.microsoft.com/sql/commun...ups/default.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:%23k0kO%23K9DHA.4060@.tk2msftngp13.phx.gbl...
> I don't seem to have a message with that title and the newsgroup is not in
> Google groups yet...
> "Jean Philippe Vankemmel" <vankemjp@.ifrance.com> wrote in message
> news:%23zh0zZG9DHA.452@.TK2MSFTNGP11.phx.gbl...
le
> what
> used
>|||Nothing there, either..
Doesn't come up in search.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:eKr4rHL9DHA.400@.tk2msftngp13.phx.gbl...
> See here - http://www.microsoft.com/sql/commun...ups/default.asp
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:%23k0kO%23K9DHA.4060@.tk2msftngp13.phx.gbl...
in
> le
reporting
believe
>|||Nothing there? I just went to it, it's toward the bottom of the page, search
for "reporting services"
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:uVp1YYL9DHA.2712@.tk2msftngp13.phx.gbl...
> Nothing there, either..
> Doesn't come up in search.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:eKr4rHL9DHA.400@.tk2msftngp13.phx.gbl...
message
not
> in
dans
> reporting
> believe
in
me
I've
>|||you should have a look at the subject "row style in matrix" in
That is what I'm looking up in that newsgroup. I can find the reporting
services newsgroup.
"Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
news:%23yp%23tcL9DHA.548@.TK2MSFTNGP11.phx.gbl...
> Nothing there? I just went to it, it's toward the bottom of the page,
search
> for "reporting services"
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
> news:uVp1YYL9DHA.2712@.tk2msftngp13.phx.gbl...
http://www.microsoft.com/sql/commun...ups/default.asp
> message
> not
> dans
wrote
> in
tell
> me
> I've
>|||Ah, OK.
I use NNTP and searched and found it, this is the excerpt: (See below)
You will need to add an expression the outer most row grouping on the
matrix.
The attached sample report demonstrates how to do this.
Key Areas:
* Matrix Properties Dialog : Groups Tab : Country Group has an expression
on it
that controls the number of rows:
=System.Math.Ceiling(RowNumber(Nothing)/5)
* Place a PageBreakAtEnd on the Matrix Properties Dialog : Groups Tab :
Country Group.
NOTE: There is a bug on this dialog in RTM version. The checkboxes to
set
PageBreakAt[Start | End] are missing. You will need to hand edit the RDL
to get this
to work. Before proceeding backup your RDL. The code snippet shows where
the
PageBreakAtEnd tag was added to the RDL:
<Grouping Name="matrix1_Country">
<GroupExpressions>
<GroupExpression>=System.Math.Ceiling(RowNumber(Nothing)/5)</GroupExpression
>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd> <<<<<< Tag added
here.
</Grouping>
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Avi" <anonymous@.discussions.microsoft.com> wrote in message
news:B9A6926F-2CBD-4C40-9471-49422C3EA39E@.microsoft.com...
> Hi
> Is there any way by which I can specify the number of rows to be displayed
on one page in matrix?
> Suppose I want only 10 rows to be displyed on one page irrespective of
collapsed or expanded state of the groups.
> How do I achieve this?
> -Avi
Ray Higdon MCSE, MCDBA, CCNA
--
"Scott Meddows" <scott_meddows_no_spm@.tsged-removeme.com> wrote in message
news:OeKWKlO9DHA.3704@.tk2msftngp13.phx.gbl...
> you should have a look at the subject "row style in matrix" in
> That is what I'm looking up in that newsgroup. I can find the reporting
> services newsgroup.
> "Ray Higdon" <sqlhigdon@.nospam.yahoo.com> wrote in message
> news:%23yp%23tcL9DHA.548@.TK2MSFTNGP11.phx.gbl...
> search
message
> http://www.microsoft.com/sql/commun...ups/default.asp
is
crit
> wrote
> tell
since
>sql

limiting the range of select result

Hi,

i'm searching how to tell to mysql server to limit
the range of a result select.
I would like to have the equivalent of the LIMIT clause in MYSQL.

I know there is TOP but it is not sufficient because it returns
only the first n lines.
I would like to tell sqlserver to begin at a certain offset.

Any idea ?

Thxyou can use a derived table

select top 10 *
from (select top 20 * from tbl order by id) as a
order by id desc

Will get the second 10 recs.

limiting query result based on AS clause

can some point me to a sql statement that would perfrom the equivalent of th
e
following statement?
select Desc, (select count (*) from CART_Items where
fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
from CART_ItemCategories
where itemcount >0
typical ouput
Desc itemcount
Cat 1 0
Cat 2 2
Cat 3 1
Cat 4 0
i would simply like to limit my results to itemcount values greater than zer
o.SELECT
ic.Desc,
COUNT(ci.fkItemCategoryID)
FROM
Cart_Items ci
INNER JOIN
Cart_ItemCategories ic
ON
ci.fkItemCategoryID = ic.pkItemCategoryID
GROUP BY
ic.Desc
HAVING
COUNT(ci.fkItemCategoryID) > 0;
"Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
> can some point me to a sql statement that would perfrom the equivalent of
> the
> following statement?
> select Desc, (select count (*) from CART_Items where
> fkItemCategoryID=CART_ItemCategories.pkItemCategoryID ) itemcount
> from CART_ItemCategories
> where itemcount >0
>
> typical ouput
> Desc itemcount
> Cat 1 0
> Cat 2 2
> Cat 3 1
> Cat 4 0
> i would simply like to limit my results to itemcount values greater than
> zero.|||thanks a million
"Aaron Bertrand [SQL Server MVP]" wrote:

> SELECT
> ic.Desc,
> COUNT(ci.fkItemCategoryID)
> FROM
> Cart_Items ci
> INNER JOIN
> Cart_ItemCategories ic
> ON
> ci.fkItemCategoryID = ic.pkItemCategoryID
> GROUP BY
> ic.Desc
> HAVING
> COUNT(ci.fkItemCategoryID) > 0;
>
>
> "Fabuloussites" <Fabuloussites@.discussions.microsoft.com> wrote in message
> news:FFF7450A-A17A-479B-B169-8DE92212E3CD@.microsoft.com...
>
>

Limitations in a query !

Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

I've read at some documentation that there is a limitation for Columns
in a table and i.e. we can have only 1024 columns per table, is this
true?
Plz help me !!

Thanx in advance.

Kind Regards,
HarryOn 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
>on "IN" clause in select query.
>for example :
>"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
>('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.

Quote:

Originally Posted by

>I've read at some documentation that there is a limitation for Columns
>in a table and i.e. we can have only 1024 columns per table, is this
>true?

Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.

Quote:

Originally Posted by

>Plz help me !!
>
>Thanx in advance.
>
>Kind Regards,
>Harry

Roy Harvey
Beacon Falls, CT|||Thanx alot Roy. :)

Roy Harvey wrote:

Quote:

Originally Posted by

On 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:
>

Quote:

Originally Posted by

Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

>
There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.
>

Quote:

Originally Posted by

I've read at some documentation that there is a limitation for Columns
in a table and i.e. we can have only 1024 columns per table, is this
true?

>
Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.
>

Quote:

Originally Posted by

Plz help me !!

Thanx in advance.

Kind Regards,
Harry

>
Roy Harvey
Beacon Falls, CT

|||Harsha, See this post

http://groups.google.com/group/comp...2da17b97723fe15
harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

Thanx alot Roy. :)
>
Roy Harvey wrote:

Quote:

Originally Posted by

On 2 Aug 2006 05:31:03 -0700, harsha.21aug@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all there,
I'm a newbee to this forum. I've a question, is there any limitation
>on "IN" clause in select query.
>for example :
>"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
>('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

There doesn't seem to be a documented limit, but if the list gets too
long it should be in a table, not in-line code.

Quote:

Originally Posted by

>I've read at some documentation that there is a limitation for Columns
>in a table and i.e. we can have only 1024 columns per table, is this
>true?

Yes, the limit is 1024 columns in a table. Any database design that
comes close to this, much less needs to exceed it, is probably
questionable.

Quote:

Originally Posted by

>Plz help me !!
>
>Thanx in advance.
>
>Kind Regards,
>Harry

Roy Harvey
Beacon Falls, CT

|||(harsha.21aug@.gmail.com) writes:

Quote:

Originally Posted by

I'm a newbee to this forum. I've a question, is there any limitation
on "IN" clause in select query.
for example :
"SELECT EMP_ID, EMP_NAME FROM EMPLOYEE WHERE EMP_ID IN
('EMP1001','EMP1002','EMP1003', etc, etc, so on)".

To add to Roy's response, be aware of that when the list grows very
long (1000 elements) compile times on SQL 2000 can be absymal. SQL 2005
appears to do better in this regard.

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

limit to where claus

Is there a maximum limit the WHere clause in sql server? How about when you
use the like operator? where can i find this information?Maximum limit? You mean a maximum limit to the number of AND's and OR's you
can include in a WHERE clause? What type of maximum limit are you looking
for specifically?
"Marty" <mbills@.ttecht(removethis).com> wrote in message
news:EC1F78AD-AB8F-4688-BCEA-13FF1CCA3865@.microsoft.com...
> Is there a maximum limit the WHere clause in sql server? How about when
> you
> use the like operator? where can i find this information?
>|||maximum limit of characters.
specifically with the LIKE operator.
"Mike C#" wrote:

> Maximum limit? You mean a maximum limit to the number of AND's and OR's y
ou
> can include in a WHERE clause? What type of maximum limit are you looking
> for specifically?
> "Marty" <mbills@.ttecht(removethis).com> wrote in message
> news:EC1F78AD-AB8F-4688-BCEA-13FF1CCA3865@.microsoft.com...
>
>|||> Maximum limit? You mean a maximum limit to the number of AND's and OR's
> you can include in a WHERE clause? What type of maximum limit are you
> looking for specifically?
(Just guessing) He might mean the size of the where clause or complexity. I
have answered similar questions from lots of Access people. See, Access has
a whole list of idiotic limits like, "The SQL Statement cannot be longer
than xxx KB of text". I think they did that just to make those backend
reporting apps extra fun to try to develop.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:edbpLrVkGHA.5020@.TK2MSFTNGP02.phx.gbl...
> (Just guessing) He might mean the size of the where clause or complexity.
> I have answered similar questions from lots of Access people. See, Access
> has a whole list of idiotic limits like, "The SQL Statement cannot be
> longer than xxx KB of text". I think they did that just to make those
> backend reporting apps extra fun to try to develop.
Ahhh. I'm not aware of any limit, and have seen humongous (albeit poorly
designed) WHERE clauses. There might be limits on the size of queries that
different tools and database connection API's can handle, although I don't
know them off-hand.

LIMIT rows with an offset

I have a query in MySQL which needs to be translated to an equivalent in MSSQL Server. The query uses a LIMIT clause with an offset.

select * from test LIMIT 10,5

(meaning from the 10th row containing 5 rows in the result set)

How can we port the same logic to SQL Server ? I know TOP can retrieve top n rows but how can we specify the offset ?

Any help is appreciated !Any such offsets or paging is meaningless without an ORDER BY clause.|||Blindman is quite correct, without an ORDER BY clause this is dangerous in any SQL dialect.

SQL-2000 doesn't have a good way to implement the functionality of MySQL's LIMIT clause. Until a new version of SQL provides a better solution, the best answer I can give you is that your code needs to provide the paging functionality.

The best way that I know to provide paging functionality is to use ORDER BY with a "rowset key" within your data and the TOP N syntax to limit the number of rows returned by a SELECT statement. Keep track of the rowset key of the last row returned, and use that as the seed for the next iteration (page) of your SELECT.

-PatP|||Appreciate your help ! Thanks !|||It must be paging day

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx