RowNumber?

I want to add a record number to each record in my report.
I don't have any grouping on my report, so not sure if using
the rownumber() function works in that case'
C.The following should work:
=RowNumber(Nothing)sql

RowNumber reset at start of each grouping

I'm creating a report where I'd like to list out the lines of the report. I
deceided to use RowNumber function but it is keeping a running total when I
use it like this: RowNumber(Nothing)
I used the report wizard to create a report and used the page grouping to
seperate pages by "team". How do I find the scope to tell RowNumber to
startover for each team?
ColinYou can set the scope of RowNumber by replacing Nothing with the name of the
group where you want the reset to occur. If your group name is Team then you
would use RowNumber("Team").
"Colin" wrote:
> I'm creating a report where I'd like to list out the lines of the report. I
> deceided to use RowNumber function but it is keeping a running total when I
> use it like this: RowNumber(Nothing)
> I used the report wizard to create a report and used the page grouping to
> seperate pages by "team". How do I find the scope to tell RowNumber to
> startover for each team?
> Colin
>
>sql

RowNumber

I understand that if using this function with "nothing" between parentheses, then the running row total never resets.

However, I am hoping to have the row counter reset when a group value changes. I tried putting both the field and the defined group name in the parentheses both with and without quotes, but I get an error.

What is the correct syntax for accomplishing this?

This may help...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=903428&SiteID=1

cheers,

Andrew

ROWNUM function

Does SQL Server 2005 or SQL Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?
please advice!
To select records from row #10 to row #20
Oracle:SELECT *FROM MyTableWHEREROWNUM>9ANDROWNUM<21
MySQL:SELECT *FROM MyTableLIMIT10,20
SQL Server:?
SELECT * FROM MyTable WHERE Row_Number() BETWEEN 10 and 20|||It is not working in SQL Express....why??|||

This one works:

SELECT

OrderID, OrderDate, RowNumberFROM(SELECT OrderID, OrderDate, ROW_NUMBER()OVER(orderby OrderID)as RowNumber

FROM

ORDERS)as tWHERE RowNumberBETWEEN 10 AND 15

Syntax in SQL Server 2005:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

sql

rownum alternate in MS-SQL

I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.
I want to get 100 rows onward to one particular data ... how can i ?This doesn't sound like a good idea... (using rownum)
Please enlighten me with the SQL you used in mySQL?
Also, what version of SQL Server are you using?|||SELECT TOP n

(Being deprecated for modification statements in SS 2008+)
SET ROWCOUNT = n

(SS 2005)
OVER () clause|||I want to get 100 rows from particular record and onward.

You need more than just TOP, don't you?
I don't like the use of rownum - I'm sure there's a better way to get what the OP wants!|||You need more than just TOP, don't you?yes, a WHERE condition, too

oh, and an ORDER BY clause, without which TOP is meaningless

:)|||I want to get 100 rows from particular record and onward. in oracle i can use rownum and in mySql i have function limit ... i want to know what is the ms-sql alternate for it.

I want to get 100 rows onward to one particular data ... how can i ?

ummmmmmmmmmmmmm

what particular row|||what particular rowthe one specified by the WHERE condition|||ummmmmmmmmmmmmm

what particular rowThat row, right there near the middle of my screen.

-PatP|||I'm feeling better now|||"Standard" for SQL 2005:

with cte
as
(select row_number () over (order by name) as num, object_id, name
from master.sys.tables)

select *
from cte
where num >= 4

Rowcount on reports

Is there a way to display a rowcount for a report?
If so what does the function look like?
Thanks in advance,
JohnWhat about RowNumber() ?
Jens Suessmeyer.
"John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D83E5AAB-FEED-4396-88BD-75E682236E76@.microsoft.com...
> Is there a way to display a rowcount for a report?
> If so what does the function look like?
> Thanks in advance,
> John|||Thanks for the quick reply Jens.
I have added a header textbox called Rows (label),
and onother with a function of =rownumber().
I am getting a compilation error of:
report1.rdl The value expression for the textbox â'RowsTextboxâ' has an
incorrect number of parameters for the function â'rownumberâ'.
If I change the function to =rownumber("Report1") I get this:
report1.rdl The value expression for the textbox â'RowsTextboxâ' uses the
function RowNumber. RowNumber cannot be used in page headers or footers.
How can I use rownumber()?
Thanks.
John
"Jens Sü�meyer" wrote:
> What about RowNumber() ?
> Jens Suessmeyer.
> "John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:D83E5AAB-FEED-4396-88BD-75E682236E76@.microsoft.com...
> > Is there a way to display a rowcount for a report?
> > If so what does the function look like?
> >
> > Thanks in advance,
> > John
>
>|||You can use =RowNumber(Nothing)
or =RowNumber("<name of report dataset>")
Look in Books Online for more information, as the function can be used with
a group name parameter and a data region name also instead of name of report
dataset. Note that the argument is case sensitive.
Charles Kangai, MCDBA, MCT
"John K" wrote:
> Thanks for the quick reply Jens.
> I have added a header textbox called Rows (label),
> and onother with a function of =rownumber().
> I am getting a compilation error of:
> report1.rdl The value expression for the textbox â'RowsTextboxâ' has an
> incorrect number of parameters for the function â'rownumberâ'.
> If I change the function to =rownumber("Report1") I get this:
> report1.rdl The value expression for the textbox â'RowsTextboxâ' uses the
> function RowNumber. RowNumber cannot be used in page headers or footers.
> How can I use rownumber()?
>
> Thanks.
> John
>
> "Jens Sü�meyer" wrote:
> > What about RowNumber() ?
> >
> > Jens Suessmeyer.
> >
> > "John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
> > news:D83E5AAB-FEED-4396-88BD-75E682236E76@.microsoft.com...
> > > Is there a way to display a rowcount for a report?
> > > If so what does the function look like?
> > >
> > > Thanks in advance,
> > > John
> >
> >
> >|||The is Rownumber, but you have to count something with it, perhaps Nothing,
so the whole expression would be =Rownumber(Nothing).
Try this.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
news:085867DD-4240-43DA-A5E8-9A16BEB14F45@.microsoft.com...
> Thanks for the quick reply Jens.
> I have added a header textbox called Rows (label),
> and onother with a function of =rownumber().
> I am getting a compilation error of:
> report1.rdl The value expression for the textbox 'RowsTextbox' has an
> incorrect number of parameters for the function 'rownumber'.
> If I change the function to =rownumber("Report1") I get this:
> report1.rdl The value expression for the textbox 'RowsTextbox' uses the
> function RowNumber. RowNumber cannot be used in page headers or footers.
> How can I use rownumber()?
>
> Thanks.
> John
>
> "Jens Süßmeyer" wrote:
>> What about RowNumber() ?
>> Jens Suessmeyer.
>> "John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
>> news:D83E5AAB-FEED-4396-88BD-75E682236E76@.microsoft.com...
>> > Is there a way to display a rowcount for a report?
>> > If so what does the function look like?
>> >
>> > Thanks in advance,
>> > John
>>|||Thanks Jens,
Got it to work.
"Jens Sü�meyer" wrote:
> The is Rownumber, but you have to count something with it, perhaps Nothing,
> so the whole expression would be =Rownumber(Nothing).
> Try this.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:085867DD-4240-43DA-A5E8-9A16BEB14F45@.microsoft.com...
> > Thanks for the quick reply Jens.
> >
> > I have added a header textbox called Rows (label),
> > and onother with a function of =rownumber().
> >
> > I am getting a compilation error of:
> >
> > report1.rdl The value expression for the textbox 'RowsTextbox' has an
> > incorrect number of parameters for the function 'rownumber'.
> >
> > If I change the function to =rownumber("Report1") I get this:
> >
> > report1.rdl The value expression for the textbox 'RowsTextbox' uses the
> > function RowNumber. RowNumber cannot be used in page headers or footers.
> >
> > How can I use rownumber()?
> >
> >
> > Thanks.
> > John
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> >> What about RowNumber() ?
> >>
> >> Jens Suessmeyer.
> >>
> >> "John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
> >> news:D83E5AAB-FEED-4396-88BD-75E682236E76@.microsoft.com...
> >> > Is there a way to display a rowcount for a report?
> >> > If so what does the function look like?
> >> >
> >> > Thanks in advance,
> >> > John
> >>
> >>
> >>
>
>|||Thanks Charles,
Got it to work.
--
"Charles Kangai" wrote:
> You can use =RowNumber(Nothing)
> or =RowNumber("<name of report dataset>")
> Look in Books Online for more information, as the function can be used with
> a group name parameter and a data region name also instead of name of report
> dataset. Note that the argument is case sensitive.
> Charles Kangai, MCDBA, MCT
> "John K" wrote:
> > Thanks for the quick reply Jens.
> >
> > I have added a header textbox called Rows (label),
> > and onother with a function of =rownumber().
> >
> > I am getting a compilation error of:
> >
> > report1.rdl The value expression for the textbox â'RowsTextboxâ' has an
> > incorrect number of parameters for the function â'rownumberâ'.
> >
> > If I change the function to =rownumber("Report1") I get this:
> >
> > report1.rdl The value expression for the textbox â'RowsTextboxâ' uses the
> > function RowNumber. RowNumber cannot be used in page headers or footers.
> >
> > How can I use rownumber()?
> >
> >
> > Thanks.
> > John
> >
> >
> > "Jens Sü�meyer" wrote:
> >
> > > What about RowNumber() ?
> > >
> > > Jens Suessmeyer.
> > >
> > > "John K" <JohnK@.discussions.microsoft.com> schrieb im Newsbeitrag
> > > news:D83E5AAB-FEED-4396-88BD-75E682236E76@.microsoft.com...
> > > > Is there a way to display a rowcount for a report?
> > > > If so what does the function look like?
> > > >
> > > > Thanks in advance,
> > > > John
> > >
> > >
> > >

rowcount in header not printing

I have figured out that you really can't place a function like
CountRows directly in a report header. So I tried creating a textbox in
the header that points to a textbox in the body that does the
calculation. There expression for this new textbox looks like the
following:
ReportItems!tbRowCount.value
Since I don't want to display two row counts in a report... I have set
the textbox in the page body to Hidden = True.
Interestingly... the following is what happens:
- In VS.NET preview: header row count renders on the first page, but
none of the others.
- In IE 6: header row count renders on all pages like I was hoping.
Yay!
- When printing from IE 6: header row count renders on the first page,
but none of the others.
What matters the most to me is that the row count shows up in on all
pages in both IE and IE printing. BTW... I tried moving the calculation
into the body and setting its repeat with to the correct dataset. This
resulted in similar behavior: print 1st page... then not again for the
rest.
So has anyone figured this out? BTW I'm using RS 2003 and not the new
version.Hi Scott,
How did you link the TextBox in the header with the one in the body?
Bryan
"scott.d" wrote:
> I have figured out that you really can't place a function like
> CountRows directly in a report header. So I tried creating a textbox in
> the header that points to a textbox in the body that does the
> calculation. There expression for this new textbox looks like the
> following:
> ReportItems!tbRowCount.value
> Since I don't want to display two row counts in a report... I have set
> the textbox in the page body to Hidden = True.
> Interestingly... the following is what happens:
> - In VS.NET preview: header row count renders on the first page, but
> none of the others.
> - In IE 6: header row count renders on all pages like I was hoping.
> Yay!
> - When printing from IE 6: header row count renders on the first page,
> but none of the others.
> What matters the most to me is that the row count shows up in on all
> pages in both IE and IE printing. BTW... I tried moving the calculation
> into the body and setting its repeat with to the correct dataset. This
> resulted in similar behavior: print 1st page... then not again for the
> rest.
> So has anyone figured this out? BTW I'm using RS 2003 and not the new
> version.
>

row_number() Help!

I have a

searching SP that uses the row_number() function to help out with

paging. But for some weird reason Im getting duplicate results when I

run the query. But if I take out the row_number() funciton I get normal

results. Here is what I have got, please help me with getting this

solved, im tearing my hair out!

WITH SearchTable AS
(
select distinct
st.id,
st.title,
st.sub_title,
st.synopsis,
st.short_code,
ROW_NUMBER() OVER (ORDER BY st.title asc) AS RowNumber

from stock as st left join
Keywords on Keywords.stock_id = st.id left join
OnlinePreviews opLow ON opLow.stock_id = st.id and opLow.[type] = 1 left join
OnlinePreviews opHigh ON opHigh.stock_id = st.id and opHigh.[type] = 2 left join
TeachersNotes tn ON tn.stock_id = st.id inner join
Stock_Subjects ss ON ss.stock_id = st.id inner join
Subjects sub ON sub.id = ss.subject_id
)

select
id,
title,
sub_title,
synopsis,
short_code,
RowNumber
from SearchTable st
Where
RowNumber between ((@.page - 1) * @.results) AND (@.page * @.results)
Order by short_code

So

thats the SQL, but keep in mind the entire SQL works fine if we take

all all references to the row_number() function. If I leave the

row_function() in then I can work out that it is my inner joins thats

the problem, if I slowly remove the inner joins and keep in the

row_number(), I figured out that the joins on the Stock_Subjects table

is the problem.

Why would this give different results when I use the row_number() funciton?

Thanks heaps to who ever solves this, I just can't figure it out!You will have to post a repro script that demonstrates the problem. ROW_NUMBER will generate unique numbers per row even if there are duplicates.|||As it turns out you are on the right track. I was selecting distinct so as try to eliminate the duplicate rows made from some joins in my query. But I also really needed to select the row_number() which, as you mentioned, was giving back a unique row number so distinct was effectivly useless.

ROW_NUMBER() function is not recognized in store procedure.

Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change.

CREATE PROCEDURE GetProductsOnCatalogPromotion
(@.DescriptionLength INT,
@.PageNumber INT,
@.ProductsPerPage INT,
@.HowManyProducts INT OUTPUT)
AS
-- declare a new TABLE variable
DECLARE @.Products TABLE
(RowNumber INT,
ProductID INT,
Name VARCHAR(50),
Description VARCHAR(5000),
Price MONEY,
Image1FileName VARCHAR(50),
Image2FileName VARCHAR(50),
OnDepartmentPromotion bit,
OnCatalogPromotion bit)
-- populate the table variable with the complete list of products
INSERT INTO @.Products
SELECTROW_NUMBER() OVER (ORDER BY Product.ProductID),
ProductID, Name,
SUBSTRING(Description, 1, @.DescriptionLength) + '...' AS Description, Price,
Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM Product
WHERE OnCatalogPromotion = 1
-- return the total number of products using an OUTPUT variable
SELECT @.HowManyProducts = COUNT(ProductID) FROM @.Products
-- extract the requested page of products
SELECT ProductID, Name, Description, Price, Image1FileName,
Image2FileName, OnDepartmentPromotion, OnCatalogPromotion
FROM @.Products
WHERERowNumber > (@.PageNumber - 1) * @.ProductsPerPage
ANDRowNumber <= @.PageNumber * @.ProductsPerPage

What version of SQL Server is this running on? ROW_NUMBER() was introduced in 2005, so any version before that won't have it.

Don

|||

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

I want to fetch records for paging purpose by its row numbers. so i tried this store proc. is there any other way to fetch without row numbers?

please reply.

|||

prasad bhanage:

Hello,I create database in sql2000 but when i got this error. I connect it with sql2005 (same database).and tried to run this store proc. but same error occurs in sql2005. I dint create database in sql2005 only open in sql2005 and tried.

Row_number() is a T-SQL enhancement that has been introduced in SQL 2005 only. If your database is in SQL 2000 you can't use any of the features introduced after SQL 2000 even if you run them using SQL 2005 tools. visithttp://msdn2.microsoft.com/en-us/library/ms186734.aspx for more information.

Now, to solve you problem as you've SQL 2000, there is one way of using temporary table for this. Your modified procedure is as below. Please make the logical changes as you wish.

CREATE PROCEDURE GetProductsOnCatalogPromotion(@.DescriptionLengthINT,@.PageNumberINT,@.ProductsPerPageINT,@.HowManyProductsINT OUTPUT)ASSELECT identity (bigint , 1 , 1 )as RowNumber , ProductID ,Name ,SUBSTRING(Description , 1 , @.DescriptionLength ) +'...'AS Description , Price , Image1FileName , Image2FileName , OnDepartmentPromotion , OnCatalogPromotioninto #ProductsFROM ProductWHERE OnCatalogPromotion = 1order by Product.ProductIDSELECT @.HowManyProducts =COUNT ( ProductID )FROM #ProductsSELECT ProductID ,Name ,Description , Price , Image1FileName ,Image2FileName , OnDepartmentPromotion , OnCatalogPromotionFROM #ProductsWHERE RowNumber > ( @.PageNumber - 1 ) * @.ProductsPerPageAND RowNumber <= @.PageNumber * @.ProductsPerPage

Hope this will help.

|||

Hi,

You need to create the database in sqlserver 2005.

Then only it will work other wise u can't use perticular keyword in Sql Server 2000.

It was newly introduced in sqlserver 2005 Only.

_____________________________________________________________

Mark as Answer If u find a solution.

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

You could use dynamic sql (execute a string using EXEC or sp_executesql).|||Thanks

ROW_NUMBER() function in SQL 2005

Can this function accept parameter in the order clause?

WITH LogEntries AS (
SELECT ROW_NUMBER() OVER (ORDER BY Date DESC)
AS Row, Date, Description
FROM LOG)

Instead of using "ORDER BY Date DESC", I would like to use "ORDER BY @.SORTCOLUMN". But I could not get this to work properly.

Thanks,

There are several options:

1. Use dynamic SQL to generate the entire SELECT statement

2. Use CASE expression in the ORDER BY clause like:

ORDER BY case @.SortColumn when 1 then col1 end,

case @.SortColumn when 2 then col2 end

3. Use various SELECT statements with UNION operator to perform branching. This is best of both worlds.

There are advantages and disadvantages to these methods. By specifying column(s) directly in ORDER BY clause any covering index can be used whereas with CASE approach you lose that advantage. Dynamic SQL approach needs to be protected against SQL injection, requires additional permissions for caller, you can use execution context in SQL2005 and so on.

|||Thank you so much

ROW_NUMBER()

Hi,

I use SQL 2005 and Visual Studio 2005.

I tried to use the ROW_NUMBER() function but I always get an error message saying ( The Over SQL construct or statement is not supported.

SELECT row_number() over (order by fullname) as ROWNUMBER, CustomerID, FullName, Address, PhoneH, PhoneMob, Area, DayNumber
FROM Customers

Thanks.

Try this sample:

Code Snippet

USE Northwind
GO

SELECT
RowNumber = row_number() OVER ( ORDER BY FirstName ),
EmployeeID,
FirstName,
LastName
FROM Employees

RowNumber EmployeeID FirstName LastName
-- -- - --
1 2 Andrew Fuller
2 9 Anne Dodsworth
3 3 Janet Leverling
4 8 Laura Callahan
5 4 Margaret Peacock
6 6 Michael Suyama
7 1 Nancy Davolio
8 7 Robert King
9 5 Steven Buchanan

If you do NOT get the same output, please verify your SQL Server version (using @.@.Version).

|||

Hi Mohamed,

Be sure that you are connecting to a 2005 instance and check that the compatibility of the database you are connecting to is 90. See sp_dbcmptlevel in BOL for more info.

AMB

|||

You probably connected the SQL Server 2000 from the Management Studio. Over clause is only accepted by SQL Server 2005.

Execute the following query..

Select @.@.VERSION

It should return as Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) .....etc.

Note:

Database Compellability level won’t affect the OVER clause. So you can utilize the OVER clause in any of the Compellability Level (60, 65, 70, 80, or 90), but it should be SQL Server 2005 or above.

|||

Manivannan.D.Sekaran wrote:

You probably connected the SQL Server 2000 from the Management Studio. Over clause is only accepted by SQL Server 2005.

Execute the following query..

Select @.@.VERSION

It should return as Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) .....etc.

Note:

Database Compellability level won’t affect the OVER clause. So you can utilize the OVER clause in any of the Compellability Level (60, 65, 70, 80, or 90), but it should be SQL Server 2005 or above.

Thanks for your kind reply,

I used (select @.@.version) and got this result.

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

I still have the same error message.

Thanks.

|||

What environment/tool are you using when the error message occurs?

Are you in a query window in SQL Server Management Studio (File->New->Query with current connection)?

Are you using Query Designer?

Are you using something in Visual Studio?

If you're not issuing the command from a query window in SQL Server Management Studio, then please try that.

|||

Yes. OVER Clause is not working with Query Designer (on Management Studio or Visual Studio).

But it is not a error. You can ignore and continue your rest of work.... On the execution time you will get the proper result

|||Yet another reason to dislike those parts of the tools. I would suggest (if you are a professional programmer, which is likely if you are in these forums that you start writing queries only in the text editor. It will greatly improve your querying skills (and you can use all of the power of SQL Server without crazy tool errors!|||

Manivannan.D.Sekaran wrote:

Yes. OVER Clause is not working with Query Designer (on Management Studio or Visual Studio).

But it is not a error. You can ignore and continue your rest of work.... On the execution time you will get the proper result

Cheers Manivannan,

Thank you very much for your great help. It worked as magic. Without your help, I would have suffered a lot.

I want also to thank all friends who replied my question. I got benefit from each and all replies.

Thanks to

Arnie Rowland

hunchback

Dalej

and Louis Davidson

'ROW_NUMBER' is not a recognized function name.

I am getting the following error while excuting following query in sqlserver 2005:

SELECT ProductName, UnitPrice,

ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS PriceRank

FROM Products

ORDER BY UnitPrice DESC

if any one know what should be done to avoid this, please let me know.

Thanks in advance,

Rajanikanth.

Check and see if the database is running in SQL Server 2000 compatibility mode. Try running these two commands:

Code Snippet

select @.@.version

exec sp_dbcmptlevel 'yourDatabaseName'

|||

As Kent stated, check that you are connecting to a SS 2005 server. You could be using the client tools shipped with 2005, but if you connect to a 2000 instance, for example, then you will not be able to use the new features from 2005. The db compatibility level does not limit you from using the new features, if it is hosted in a 2005 instance.

How to identify your SQL Server version and edition

http://support.microsoft.com/default.aspx?scid=kb;en-us;321185

AMB

sql

'ROW_NUMBER' is not a recognized function name.

Hi experts,
recently i download the SQLExpress 2005 version to do some testing,
when i try to run a new feature which all ROW_NUMBER, but SQL give me
error
'ROW_NUMBER' is not a recognized function name.
i wondering after install the 05, we need to add-on some package to
support this kind of feature,. which is not default '
* the sample is from
http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
pls give advise, many thanksJust the routine check. I hope you are not connecting to a SQL Server 2000
instance from the SQL Srvr Management Studio express edition.|||Check your database compatibility level
exec sp_dbcmptlevel '<Name of db>'
HTH
Kalen Delaney, SQL Server MVP
"XJ" <ianyian@.hotmail.com> wrote in message
news:1148698630.294392.99660@.g10g2000cwb.googlegroups.com...
> Hi experts,
> recently i download the SQLExpress 2005 version to do some testing,
> when i try to run a new feature which all ROW_NUMBER, but SQL give me
> error
> 'ROW_NUMBER' is not a recognized function name.
> i wondering after install the 05, we need to add-on some package to
> support this kind of feature,. which is not default '
> * the sample is from
> http://msdn.microsoft.com/sql/learn...tsqlenhance.asp
> pls give advise, many thanks
>|||Hi Kalen,
i catch u, which give me 80, yes i agree that i have sql 2000
before, but i already uninstall it, rght now under the service name
call XJ\SQLEXPRESS.
and i have use "Microsoft SQL Server Management Studio Express",
but even i try to create a new DB, still give me 80,. pls give some
idea how can i create a DB which have 2005 feature under "studio
express" or any other way ,. .. many thanks|||Hi Kalen , Omnibuzz
Bcos the instance make me some confuse ,. finally i can
connect ! ya ,. many thanks !!!!

Row_Number function in WHERE clause

What is the reason that you cannot use the results of the ROW_NUMBER function in a WHERE clause? I can achieve the results that I want by using a derived table, but I was just looking for the exact reason.

I have my speculations that it is because the results of ROW_NUMBER are applied after the rows are selected and filtered, but I'd like something definitive.

Thanks a bunch in advance.

That would be the exact reason. The ROW_NUMBER function numbers output rows, so if you used it in the WHERE it would have to ignore rows that would not meet the criteria to be output.

Otherwise if it applied at the FROM level, there might be gaps in the sequence.

|||Louis,

Thank you very much for the answer.

Row_Number Function

i want distinct rows from the table
i want to use "Row_Number" function with this
so
how can i use "Row_Number" function when distinct keyword is used
so i get the distinct rows as well as Row Numbers

suppose my table is as below:

Row Nos id class name

11mca sasfdfj
21mca jklj
32mca jljlj
42mca jkljljl
53mcs gghgh
64mca gghgh
73mcs gghghUsing distinct keyword is equivalent to using group by specifying all fields, so you can just use group by, and specify ROW_NUMBER as usual

Row to Column?

All:
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col

Row to column

Is there a row to column function?

I need to convert some rows into columns in a stored proc.

An y ideas.

Thanks,

Gene

If you're using SQL2005 there is the PIVOT/UNPIVOT functions which are explained nicely in Books Online...
|||

if there is multiple columns need to be pivoted, I recommand to use the legacy approach rather new PIVOT operator,

Code Snippet

Create Table #UnPivot

(

Year int,

Product int,

Sales int,

Qty int

)

Insert Into #UnPivot Values(2004,1,28,67);

Insert Into #UnPivot Values(2005,1,15,20);

Insert Into #UnPivot Values(2006,1,50,30);

Insert Into #UnPivot Values(2004,2,5,67);

Insert Into #UnPivot Values(2005,2,6,20);

Insert Into #UnPivot Values(2006,2,10,30);

Select

Product

,Max(Case When Year=2004 then Sales End) [2004-Sales]

,Max(Case When Year=2004 then Qty End) [2004-Qty]

,Max(Case When Year=2005 then Sales End) [2005-Sales]

,Max(Case When Year=2005 then Qty End) [2005-Qty]

,Max(Case When Year=2006 then Sales End) [2006-Sales]

,Max(Case When Year=2006 then Qty End) [2006-Qty]

From

#UnPivot

Group By

product

Drop Table #UnPivot

sql

Row Numbers for Group

I have a table with 2 Groups and I want to number only Group2 with row
numbers. I put in a RowNumber(Nothing) function, and i'm getting something
like this:
Group 1
11 Group 2
14 Group 2
28 Group 2
Group 1
35 Group 2
etc...
What can I do to get the numbers to show up correctly?
Thanks!problem solved!
=RunningValue(Fields!fieldname.Value, CountDistinct, Nothing)
"jmann" wrote:
> I have a table with 2 Groups and I want to number only Group2 with row
> numbers. I put in a RowNumber(Nothing) function, and i'm getting something
> like this:
> Group 1
> 11 Group 2
> 14 Group 2
> 28 Group 2
> Group 1
> 35 Group 2
> etc...
> What can I do to get the numbers to show up correctly?
> Thanks!sql

row number

how or what function can we use in sql server 2005 or 2000 to return the
corresponding row number of a record in select statement
for example
SELECT name, xxxxx as Number
FROM TableX
xxxxx - is the function or keyword that returns the corresponding row number
of the select statement.
the result set could be..
Name Number
John Doe 0
Jane Doe 1
0, 1 are the corresponding row number..
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787http://www.aspfaq.com/2427

> John Doe 0
> Jane Doe 1|||Jose,
In SQL Server 2000, you can derive row numbers using sub query like
use pubs
GO
--SELECT * FROM jobs
Select job_desc, (Select Count(*) + 1 FROM jobs B
WHERE B.job_desc < A.job_desc) AS RecNo
FROM jobs A
ORDER By job_desc
SQL Server 2005 has an inbuilt ROW_NUMBER function.
SELECT ROW_NUMBER() OVER (ORDER BY job_desc ASC) as rownum, * FROM jobs
For more info see
http://toponewithties.blogspot.com/...es.blogspot.com
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||Using a identity field you obtain it. If you haven't I think exists a
internal rowid or something like that which provide us the row number.
see you,
"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row numb
er
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||In SQL Server 2005, look up the ROW_NUMBER() function.
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||> If you haven't I think exists a
> internal rowid or something like that
There are internal row identifiers but these are not exposed to you, so you
can't use them in display.
Typically, the best way to present row numbers is to tack them on in the
presentation layer, since that's the only place where you *have to* loop
through and handle every single row anyway. Forcing the row numbers to be
generated in the database puts unnecessary strain there and turns a simple
query into either a subquery that is evaluated per row, or a mess with
pre-population into a temp table or table variable.|||>Using a identity field you obtain it
That behavior is not guaranteed.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
> Using a identity field you obtain it. If you haven't I think exists a
> internal rowid or something like that which provide us the row number.
> see you,
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||http://support.microsoft.com/defaul...b;EN-US;q186133
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||If one of the columns in query is unique, the following calculates a
sequence number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:54FFB330-1CCC-49FC-AD92-DF007876358C@.microsoft.com...
> how or what function can we use in sql server 2005 or 2000 to return the
> corresponding row number of a record in select statement
> for example
> SELECT name, xxxxx as Number
> FROM TableX
> xxxxx - is the function or keyword that returns the corresponding row
> number
> of the select statement.
> the result set could be..
> Name Number
> John Doe 0
> Jane Doe 1
> 0, 1 are the corresponding row number..
>
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||I disagree. While it is true that using the IDENTITY function in a SELECT
INTO with an ORDER BY clause doesn't guarantee that the order of the
IDENTITY values match the order specified in the ORDER BY clause, using an
INSERT...SELECT...ORDER BY to insert into a temporary table or table
variable with an IDENTITY column will always work correctly. See KB273586.
An obvious improvement, however, is the ROW_NUMBER() function in SQL Server
2005, which eliminates the need for the self-join or the intermediate temp
table or table variable.
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OEtZ0dKqFHA.3192@.TK2MSFTNGP10.phx.gbl...
> That behavior is not guaranteed.
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:30C1DECC-AF8C-4662-8EB3-3A1F0E3CBE26@.microsoft.com...
the
>