I want to show how many records populated in the groupby clause, i want to show the number of rows value in the group footer.
Thank you very much.
If you put this in the group footer, you should see the count for each group.
=CountRows()
Hope this helps.
Jarret
sql
Code Snippet
CREATE FUNCTION [dbo].[Ready]
(
-- Add the parameters for the function here
@.ProductPrm uniqueidentifier
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @.ProductNum int;
DECLARE @.Status bit;
-- Add the T-SQL statements to compute the return value here
SELECT @.ProductNum =COUNT(*)
FROM tblProduct
WHERE ProductID = @.ProductPrm
-- Return the result of the function
CASE WHEN @.ProductNum >= 1 THEN @.Status = 'False' ELSE @.Status = 'True' END
RETURN @.Status
END
Code Snippet
Msg 156, Level 15, State 1, Procedure RestoranHazirMi, Line 25
Incorrect syntax near the keyword 'CASE'.
Msg 102, Level 15, State 1, Procedure RestoranHazirMi, Line 29
Incorrect syntax near 'END'.
CASE WHEN is a statement level construction, not flow control
You could use
Code Snippet
CREATE FUNCTION [dbo].[Ready]
(
-- Add the parameters for the function here
@.ProductPrm uniqueidentifier
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @.ProductNum int;
DECLARE @.Status bit;
-- Add the T-SQL statements to compute the return value here
SELECT @.Status = CASE WHEN COUNT(*)>=1 THEN 'False' ELSE 'True' END
FROM tblProduct
WHERE ProductID = @.ProductPrm
RETURN @.Status
END
OR (IF/ELSE for flow control):
Code Snippet
CREATE FUNCTION [dbo].[Ready]
(
-- Add the parameters for the function here
@.ProductPrm uniqueidentifier
)
RETURNS bit
AS
BEGIN
-- Declare the return variable here
DECLARE @.ProductNum int;
DECLARE @.Status bit;
-- Add the T-SQL statements to compute the return value here
SELECT @.ProductNum =COUNT(*)
FROM tblProduct
WHERE ProductID = @.ProductPrm
-- Return the result of the function
IF (@.ProductNum >= 1)
SET @.Status = 'False'
ELSE
SET @.Status = 'True'
RETURN @.Status
END
|||thanks dude. I've learned it now.sqltsDATETIME
usernameVARCHAR(16)
...
GPS data
...
I want to select the most recent GPS data for each distinct user.
That is, the table will have may records for a given username, but I
only want the most recent for each one.
For a single user I know I can do
SELECT TOP 1 * from <table> order by ts desc
But I want a set of results that effectively does this for
all users, and I can't work out the necessary query/subquery I
should be using.
I'm sure I'm missing something fairly obvious, so usual newbie
disclaimers apply.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/What is the primary key? I'll assume the key consists of (ts,
username), in which case the folllowing should do what you want:
SELECT ts, username, ... /* other columns */
FROM YourTable AS T
WHERE ts =
(SELECT MAX(ts)
FROM YourTable
WHERE username = T.username)
It really helps if you include DDL with questions like this (basically
a CREATE TABLE statement, including keys and constraints). The exact
table structure may make a big difference to the possible solutions.
The usual recommendation that you shouldn't use SELECT * in production
code also applies.
--
David Portas
SQL Server MVP
--|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications. Even pseudo-DDL is better than narratives. Is this
what you meant?
CREATE TABLE Foobar
(event_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(16) NOT NULL,
stuff_1 INTEGER NOT NULL,
stuff_2 INTEGER NOT NULL,
..
stuff_n INTEGER NOT NULL,
PRIMARY KEY (user_name, event_time));
SELECT F1.*
FROM Foobar AS F1
WHERE F1.event_time
= (SELECT MAX(f2.event_time)
FROM Foobar AS F2
WHERE F1.user_name = F2.user_name);
-- use column names in production code, not SELECT *.|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:
>What is the primary key? I'll assume the key consists of (ts,
>username), in which case the folllowing should do what you want:
>SELECT ts, username, ... /* other columns */
> FROM YourTable AS T
> WHERE ts =
> (SELECT MAX(ts)
> FROM YourTable
> WHERE username = T.username)
Thanks
>It really helps if you include DDL with questions like this (basically
>a CREATE TABLE statement, including keys and constraints). The exact
>table structure may make a big difference to the possible solutions.
Point noted.
>The usual recommendation that you shouldn't use SELECT * in production
>code also applies.
I realise this. That was just me being lazy (although for some of my
selects I do want the entire row).
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/|||"--CELKO--" <jcelko212@.earthlink.net> wrote:
>Please post DDL, so that people do not have to guess what the keys,
>constraints, Declarative Referential Integrity, datatypes, etc. in your
>schema are. Sample data is also a good idea, along with clear
>specifications. Even pseudo-DDL is better than narratives. Is this
>what you meant?
Sorry. Will do in future.
Thanks for the solution.
--
HTML-to-text and markup removal with Detagger
http://www.jafsoft.com/detagger/sql
hi
i m using row count in order to get first 16 rows from a specific table...now i want to get rows from row no. 16 to 32 (or any no which i want)...can any one tell meee how can i query it using sql server 200
HI,
do it as
for n to n1 records do as
select top n1-n * from
(
select top n1 * from
)
order by key desc
|||
If your total result set is small enough (> 1000 records) you could insert into a table variable with an identity column and then select back out referencing the identity column in the where clause. For example:
DECLARE @.tblTable TABLE
(
TableID INT IDENTITY(1,1)
,OtherID INT
,Value VARCHAR(50)
)
INSERT @.tblTable (OtherID, Value) VALUES (1, 'One')
INSERT @.tblTable (OtherID, Value) VALUES (2, 'Two')
INSERT @.tblTable (OtherID, Value) VALUES (3, 'Three')
INSERT @.tblTable (OtherID, Value) VALUES (4, 'Four')
INSERT @.tblTable (OtherID, Value) VALUES (5, 'Five')
DECLARE @.Start INT, @.End INT
SELECT @.Start = 1, @.End = 3
SELECT *
FROM @.tblTable
WHERE TableID BETWEEN @.Start AND @.End
|||It seems that you are trying to do pagination in database.
Below is the code snippet for a stored procedure. This takes page number and numbers of records in a Page.
CREATE PROCEDURE Pagination
@.Page int,
@.Size int
AS
DECLARE @.Start int, @.End int
BEGIN TRANSACTION GetDataSet
SET @.Start = (((@.Page - 1) * @.Size) + 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
SET @.End = (@.Start + @.Size - 1)
IF @.@.ERROR <> 0
GOTO ErrorHandler
CREATE TABLE #TemporaryTable
(
Row int IDENTITY(1,1) PRIMARY KEY,
Project varchar(100),
Buyer int,
Bidder int,
AverageBid money
)
IF @.@.ERROR <> 0
GOTO ErrorHandler
INSERT INTO #TemporaryTable
SELECT ...
-- Any kind of select statement is possible with however many joins
-- as long as the data selected can fit into the temporary table.
IF @.@.ERROR <> 0
GOTO ErrorHandler
SELECT Project, Buyer, Bidder, AverageBid
FROM #TemporaryTable
WHERE (Row >= @.Start) AND (Row <= @.End)
IF @.@.ERROR <> 0
GOTO ErrorHandler
DROP TABLE #TemporaryTable
COMMIT TRANSACTION GetDataSet
RETURN 0
ErrorHandler:
ROLLBACK TRANSACTION GetDataSet
RETURN @.@.ERROR
Regards
Sachin
Hi,
Can anyone tell me how to get a scrollbar inside a subreport.I'm using SQL Server 2005 Business Int. Solution.I'm having more than 1000 rows for a subreport.I want to fix the width and hieght of the subreport so that i can get a scrollbar in the subreport.Please, help me regarding this.
Thanks,
Saradhi
This is easy to do with a report viewer using Visual Studio 2005. I'm not sure how to do this using SQL Server 2005 BIS.|||
Hi,
Yes.It can be done in Visual Studio 2005 using reportviewer control.But i don't know with SQL Server 2005 BIS. Anyway, thanks for the reply.
Thanks,
Saradhi
I want the number of rows returned by this query.
How can i get the number of rows?
Please help me..
waiting for your replies..
Prem
(premratan@.hotmail.com)Select count (*) as "row count" from (select <any query here>) as t
For some reason, the final table alias "as t" is required.
Goetz Graefe
"Prem" <premratan@.hotmail.com> wrote in message
news:2f7d06ff.0311111515.2a2a040c@.posting.google.c om...
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.CategoryName, Products.ProductName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
> INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
> WHERE
> (Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.CategoryName, Products.ProductName
> I want the number of rows returned by this query.
> How can i get the number of rows?
> Please help me..
> waiting for your replies..
> Prem
> (premratan@.hotmail.com)|||Refer to @.@.ROWCOUNT in SQL Server Books Online. If you are using ADO in your
application, then you can use the recordset's RecordCount property to get
the value at the client side.
--
-- Anith
( Please reply to newsgroups only )|||premratan@.hotmail.com (Prem) wrote in message news:<2f7d06ff.0311111515.2a2a040c@.posting.google.com>...
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.CategoryName, Products.ProductName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.ProductID)
> INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
> WHERE
> (Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.CategoryName, Products.ProductName
> I want the number of rows returned by this query.
> How can i get the number of rows?
> Please help me..
> waiting for your replies..
> Prem
> (premratan@.hotmail.com)
After running the query, you can do this:
select @.@.rowcount
If you need to use the value later, you can put it in a variable:
set @.rows = @.@.rowcount
Simon
thanks
Why exactly do you need the RID?
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com
|||HI WesleyB
I have no concret need now.
Just want to know where it store?
I use dbcc page command to oversee the structure of the data page.
I can see many data row .... but can't see the RID....
|||The RID is not stored. It is calculated from the PageId and the SlotId on the page, i.e. Slot 17 on Page 1:29 has RID 1:29:17.Thanks,
Hi
I'm using the SQL Server 2005 Express edition.
I've manually created some rows in a table "Books".
If I right-click on "dbo.Books" and choose "Script Table As --> Create To --> New Query Editor Window", I get the SQL for creating that table.
But how do I get the INSERT-SQL for the rows already inserted?
Kind Regards
hi,
there's no support for that task, you have to do it your self..
or you can have a look at a free prj of mine, amInsert, available at http://www.asql.biz/en/Download2005.aspx
regards
|||Hi Andrea
Thanks for your ImInsert!
It worked perfectly!
Kind Regards
