Getting The Number Pf Rows In A Query Result
Sep 1, 2004
My site have a complicated search, the search give the results in two stages- the first one giving the number of results in each section:
"In the forums there is X results for the word X
In the articles there is X results...."
And when the user click one of those lines, the list shows the specific results in that section.
My problem is that I don't know how to calculate the first part, for now I use dataset, and table.rows.count to show the number of results in each section. Since my site have more then ten, it looks like a great waste to fill such large dataset (in some words it can be thousands of rows in each section) only for getting the number of rows…
Are there is a sql procedure or key word that will give me only the number of results (the number of times that specific word showing in the columns?)
Great thanks
View 1 Replies
ADVERTISEMENT
May 22, 2008
Is there any way I can add record number to my query result?
Exp:
Name Add tel
A 1 st 123456
B H ave. 987456
I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456
Thanks
View 9 Replies
View Related
Nov 7, 2007
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
View 1 Replies
View Related
Mar 5, 2015
I want to make a query with the result like this, where Seqno is the result query which has repeating sequence number for same Field1
Field1Field2SeqNo
BVFUSBVFUS011
BVFUSBVFUS021
BVFUSBVFUS031
BVFRPBVFRP012
BVFRPBVFRP022
[Code] ....
I want the result also no ordered by Field1. it just as the natural order of the table..
View 3 Replies
View Related
May 30, 2007
Working in Sql Server 2005,got 3 different table.Need to fetch the list of persons.A person can belong to different categories.When i am using inner join
on tables i am getting the duplicate rows because a perosn can belong to different categories.I want that there should be only onoe
row for the person and the different categories he belongs to can come up in single field as comma sepatated string
Now the results are like this:
firstname lastname adress category
abc xyz aaa a
abc xyz aaa b
abc xyz aaa c
I want like below:
abc xyz aaa a,b,c
I am thinking of using cursors in the stored procedure.Can you provide me the solution of this including the stored procedure..
View 7 Replies
View Related
Sep 23, 1999
How to get result from Query by group of (n) rows ?
- Is cursors the better way (and the only) ?
-Is there something as ROWNUM in Oracle or LIMIT in MySql ?
Example plz !
Thanks.
Alex
View 3 Replies
View Related
Jan 22, 2014
I worked with someone else to create a query that gives us the age of a stock. How long it has been in the warehouse since the Purchase order date (without completely selling out). It does exactly what I want, the problem is that it only accepts 1 row as a result.
The error message I get is:
quote:Msg 512, Level 16, State 1, Line 4
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So my question is; can this code be modified to pass it multiple SKUS and run a report on every item currently in stock?
SELECT TOP 1
@skuVar, CAST(GETDATE()-ReceivedOn AS INT) AS 'Age'
FROM
(SELECT pir.id AS id,aggregateQty AS aggregateQty,-qtyreceived as qtyreceived, (aggregateQty - qtyreceived) AS Diff, ReceivedOn AS ReceivedOn
,(
SELECT SUM (PurchaseItemReceive.qtyreceived)
FROM bvc_product pp
[code].....
I use Microsoft SQL 2008
View 1 Replies
View Related
Dec 3, 2015
I have 3 tables:
TABLE [dbo].[Tbl_Products](
[Product_ID] [int] IDENTITY(1,1) NOT NULL,
[Product_Name] [nvarchar](50) NOT NULL,
[Catagory_ID] [int] NOT NULL,
[Entry_Date] [date] NOT NULL,
[Code] ....
I am using this query to get ( Product name from tbl_products , Buy Price - Total Price- Total Quantity from Tbl_Details )
But am getting a multiple result if the order purchase has more than 1 item :
SELECT DISTINCT B.Product_Name,A.AllPieceBoxes,
A.BuyPrice,A.TotalPrice,A.BuyPrice
FROM
Tbl_Products B INNER JOIN Tbl_PurchaseHeader C
ON C.ProductId=B.Product_ID INNER JOIN Tbl_PurchaseDetails A
ON A.PurchaseOrder=C.purchaseOrder
WHERE A.PurchaseOrder=3
View 5 Replies
View Related
Jan 28, 2008
Hi,
Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005.
I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
ORDER BY t.topicid ASC,th.lastpostdate DESC
Whose result set is as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
1
Java Overall
a@b.com
2008-01-27 14:44:29.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
On modifying the query to:-
SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads
FROM
Forums f FULL JOIN Topics t ON f.forumid=t.forumid
FULL JOIN Threads th ON t.topicid=th.topicid
GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate
HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid)
ORDER BY t.topicid ASC,th.lastpostdate DESC
I get the result set as below:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
I want the result set as follows:-
forumid
topicid
name
author
lastpostdate
NoOfThreads
1
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above.
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
View 7 Replies
View Related
Dec 10, 2007
Hi
I have a database with 150 tables. I want to make a query ranking those tables by table size and/or number of rows.
Is this possible?
Many thanks
ds9
View 6 Replies
View Related
Apr 20, 2015
I need a column in my query results that just numbers the rows sequentially (i.e. 1, 2, 3). How can I do that?
View 6 Replies
View Related
Mar 30, 2006
which is the most efficient query to find out the total number of rows in a table other than using - SELECT COUNT(*) ... in query
View 10 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Dec 25, 2006
While using Aggregate Transformation to group one column,the rows of output sometimes larger than the rows returned by a T-SQL statement via SSMS.
For example,the output of the Aggregate Transformation may be 960216 ,but the
'Select Count(Orderid) From ... Group By ***' T-SQL Statement returns 96018*.
I'm sure the Group By of the Aggregate Transformation is right!
But ,when I set the "keyscale" property of the transformation,the results match!
In my opinion,the "keyscale" property will jsut affects the performance of the transformaiton,but not the result of the transformation.
Thanks for your advice.
View 2 Replies
View Related
Feb 13, 2001
HI,
I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes
I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record
Thanks for your help
Ali
View 1 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
May 30, 2008
like (###) ###-####.
I know that
SELECT phone
FROM author
however, the results will be like
408 496-7223.
but I do not know how to format the phone number.
can you help me to figure it out please?
thanks.
View 4 Replies
View Related
Nov 13, 2007
Hello, I am very, very new to the world of SSIS and from a totally different platform (iSeries), learning, digging and finding golden nuggets as I go. If this is not the forum to which I should be addressing my dilemma, please let me know as to whom or where I should direct it to. I have presented this question at two other forums, and I find it difficult to believe that no one is able to help me.
My two questions are as follows:
Question One:
I am presently encountering the following exception.
SSIS package "DW.CUST_CNCL_ORD.dtsx" starting.
Error: 0xC00291E2 at BP Parter Exec SQL, Execute SQL Task: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Task failed: BP Parter Exec SQL
Warning: 0x80019002 at DW CUST_CNCL_ORD: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "DW.CUST_CNCL_ORD.dtsx" finished: Failure.
Try as I do to find information regarding this exception, I am not able to. Can anyone share their wisdom on this topic?
Question two:
Is there a book that you may recommend that will contribute to my learning this new platform and evironment? I presently have the SQL Server 2005 integration services book.
Sample code follows:
SELECT BUS_YY_ID AS RBUS_YY_ID, BUS_PRD_ID AS RBUS_PRD_ID, BUS_PRD_STRT_DT AS RBUS_PRD_STRT_DT,
BUS_PRD_END_DT AS RBUS_PRD_END_DT
FROM BP.BUS_PRD_DT
WHERE (BUS_YY_ID = ?)
The first parameter is defined as parameter 0 - Direction is Input
Subsequent parameters are defined as follows:
Variable Name Direction Parameter Name Current_Year Input 0
RBUS_YY_ID Output 1
RBUS_PRD_ID Output 2
RBUS_PRD_STRT_DT Output 3
RBUS_PRD_END_DT Output 4
Result is defined as follows:
Result Name Variable Name
1 RBUS_YY_ID
2 RBUS_PRD_ID
3 RBUS_PRD_STRT_DT
4 RBUS_PRD_END_DT
Thank you in advance for any information you may provide.
Crodrig
View 26 Replies
View Related
Jul 4, 2012
Code:
SELECT ((ACOS(SIN(34.37769 * PI() / 180) * SIN(latitude * PI() / 180)
+ COS(34.37769 * PI() / 180) * COS(latitude * PI() / 180) * COS((132.404738 - longitude)
* PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS ranges FROM completeyuubinwithlatlon having ranges<=3
First, I have that code whenever i disregard the "having ranges<=3" it selects record but when I include it it returns error. The problem is I want to just select records with ranges less than 3 miles..how do I do that.. and I want to count the number of selected rows and store it on a column on the same table.
View 3 Replies
View Related
Sep 6, 2000
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
View 4 Replies
View Related
Nov 29, 2001
work_id Work_item_code
--------------------------------
3 Sup
14 100001
5 100002
16 100003
8 IWSM
9 ES3000
10 AppDev
11 100004
I want to have a counter on this select like
work_id Work_item_code counter
----------------------------------------
3 Sup 1
14 100001 2
5 100002 3
16 100003 4
8 IWSM 5
9 ES3000 6
10 AppDev 7
11 100004 8
I can use rownum in Oracle, what do I do to get this in SQL server ?
Thanks Sonali
View 2 Replies
View Related
Feb 12, 2014
I have two tables that I am pulling data from to insert into a third table.
Table 1:
Organization Address Date
Name 404 St. 12/31/1999
Table 2:
Organization Software Type Quantity
Name SW1 5
Name SW2 6
Name SW3 7
Name SW4 8
My query looks something like this:
INSERT INTO Organization SW Reqs (Name, Address, Date, SW1 Quantity, SW2 Quantity, SW3 Quantity)
SELECT Table 1.Name, Table 1.Address, Table 1.Date,
(Select Table 2.Quantity Where Table 2.Software Type = 'SW1'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW2'),
(Select Table 2.Quantity Where Table 2.Software Type = 'SW3')
FROM Table 1, Table 2
WHERE Table 1.Organization = Table 2. Organization
Sadly, this query gives me four rows, one for each Software Type. I have tried putting DISTINCT after my SELECT, but that narrows it down to two rows only. I would really like to have just a one row result per organization.
View 2 Replies
View Related
Jun 20, 2007
In the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1071904&SiteID=1 mike.groh stated that you would define the result set as a data set variable through an execute sql task. He then pushed the variable out as a dataset type. (ds = CType(Dts.Variables("Email_CurrentDataset").Value, DataSet)
How would this type of object be declared as a user variable? Is it of type object? What is the logic behind converting a result set to a data set?
Specifically I am having problems with converting / casting the result set to a data set in that I am getting an error stating that it cannot convert the com object to a data.dataset class...
Imports ADODB
Imports System
Imports System.Xml
Imports System.Collections
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts
Imports Microsoft.SqlServer.Dts.DtsClient
Public Class ScriptMain
Public Sub Main()
Dim ds As DataSet
Dim dr As DataRow
Dim str As String
Dim dtm As String
Dim s As String
Try
ds = CType(Dts.Variables("resultSet").Value, DataSet) ' <-- Error on converting
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
View 1 Replies
View Related
Jun 13, 2006
Can we select "last 10 rows" from the result set. (sql server 2000)
Something of an opposite of "top" functionality
View 3 Replies
View Related
Sep 20, 2007
I am trying to set up an Execute SQL Task that will feed into a foreach loop for processing of data. I am using an ADO.NET Connection with Direct input with full result set. I have tried using "select * from <table>" and also "select <column1>, <column2> from <table>". I have user variables set in the package and have the table cfolumn names listed in result name and the appropriate package variable in variable name on the Result Set tab. There is a total of 53 columns per row and the number of rows that should be returned can be anywhere from none to several hundred. Upon trying to run the package, I am receiving
Error: 0xC00291E2 at Execute SQL Task, Execute SQL Task: There is an invalid number of result bindings
returned for the ResultSetType: "ResultSetType_Rowset".
Anyone have any ideas here? Thanks in advance!
View 2 Replies
View Related
Aug 20, 2007
In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)
Each paragraph of text (SectionText) is in its own row
(SectionNo) Each primary document has a DocumentTypeID of 1 with
three subdocument types (2=Index, 3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo: SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo
For each row that contains the search term, I would like to
return the full document (all rows as parapraphs within one row of
returned data). In other words, I want to reconstitute the full
document as it existed prior to being inserted into the database with
paragraph separation. For exampe, if the search term is in row 3
of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of
that document in one block of text that retains paragraph format
(preferablly with a line break and carriage return between
paragraphs). How can this be done?
View 2 Replies
View Related
Sep 20, 2001
Hello!
I have two similar servers (hardware) with
SQL Server 2000 (SP1) on Win2000 (SP2).
(sp_helpsort - default)
When I run the same SELECT statement from Query Analyzer on each machine
I get different rows order in result Grid
(the order of rows stays the same for each machine but different for
machine1 and machine2)
SELECT table1.a,
table1.b,
table1.c,
table1.d
FROM table2,
table1,
table3
WHERE table2.nTestDefnId = 1034
AND table2.nDefnId = table1.nDefnId
AND table3.szClinDiscType = 'X'
AND table2.nDiscId = table3.nDiscId
What is a reason for such behavior and how I can fix it?
Thank you in advance,
Elena.
View 1 Replies
View Related
May 19, 2014
How to get the result set:
AwardNumber AwardDt AwardType_RSS AwardQty_RSS
A1 01/01/2004 RSS 100
QUERY:
create table structure(
AWARDNUMBER VARCHAR(10),
AWARDDT DATEtime,
AWARDTYPE CHAR(3),
QTYAWARDED DECIMAL(20,6)
)
insert into structure
select 'A1','1/1/2004',null,null
union all
select null,null,'RSS',100
current output:
AWARDNUMBERAWARDDT AWARDTYPEQTYAWARDED
NULL NULL RSS 100.000000
A1 2004-01-01 00:00:00.000 NULL NULL
View 1 Replies
View Related
May 26, 2008
Hi,
for example i need to update table with top 5callers who called the most to one number:
With subselect i get results:
Number Caller Times
555-11111 555-11112 10
555-11111 555-11113 9
555-11111 555-11114 8
555-11111 555-11115 7
555-11111 555-11116 6
And now i need update table and get this results:
Number Top1Caller Top1Caller Top1Caller Top1Caller Top1Caller
555-11111 555-11112 555-11113 555-11114 555-11115 555-11116
And i need to do this with every number in database
View 1 Replies
View Related
Apr 30, 2008
Hi I have a 'settings' table similar to:
* Setting_ID
* SettingGroup
* SettingSubGroup
* SettingKey
* SettingValue
example data:
1, Comms, Com1, Speed, 9600
2, Comms, Com1, Parity, N
3, Comms, Com1, DataBits, 8
4, Comms, Com1, StopBits, 1
5, Comms, Com2, Speed, 2400
6, Comms, Com2, Parity, E
7, Comms, Com2, DataBits, 7
8, Comms, Com2, StopBits, 2
I want to have a query like
SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup
where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like:
SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings
----------------
Com1, 9600, N, 8, 1
Com2, 2400, E, 7, 2
I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?
View 1 Replies
View Related
May 20, 2008
In MySQL we use "SELECT (....) LIMIT 0, 10" to only return the first 0 to 10 records. Alternatively we could do "LIMIT 10, 20" to return the 10th to 20th records.
What's the equivilent of this in SQL Server?
Thanks
View 4 Replies
View Related
May 7, 2012
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name,
a.cli_first AS First_Name, a.cli_middle AS Mid_Init,
b.city AS City, b.county AS County, b.state AS State
from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b
on a.client_id = b.client_id
inner join ECBH.dbo.tbl_client_insurance c
on a.client_id = c.client_id
inner join ECBH_TEST.dbo.tbl_GEF_County d
on b.county = d.COUNTY_NAME
where c.plan_id = 4
order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
View 5 Replies
View Related
Jun 26, 2014
I am trying to run queries on a table (table has zero rows). Inspite of giving 0 rows returned the query keeps on running and I have to cancel it. I tried inserting a dummy row into the table but even the insert operation is taking too long.Every query which I hit on the table just keeps on running without giving any result.
But this is not the case with other tables in the database.They are all running fine giving proper results. But this one table is behaving funny.
View 3 Replies
View Related
