In MSSQL, is there a way to count the number of instances of a
substring within a string, so that I can sort by that?
For example:
table tst contains one column: tst_data
if tst_data = "the man with the plan"
I'd want a function that counts the occurances of "the"
count_substring(tst_data,'the') = 2
Basically, I'm making a search engine and I'd like to put the most
relavent hits at the top of the page.
I have a smalldatetime column in my sql2000 database table and am sorting ascending on a smalldatetime.
The problem is my results are not in desc order like I want.
Now 5/15/2007 is showing up after 5/9/2007.
I need my dates to sort from the most recent to the latest.
Any help is appreciated.
Hi,
ORDER BY will sort string (varchar) field which "11" < "2", is there any way I can sort them as number so "11" > "2"?
what if there is character inside, like "11a", "2b", in this case, it should sort like, "1" "2" "11" "1a" "2a" "11a" "1b" ....
Is this possible?
also, I tried to google this but not find anything help. what's best keyword to use?
Thank you,
Wes
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
can anyone give any code snippets?
Hi all,Is there a way of counting the number of results/rows from an SqlDataSource which uses a select statement like: "SELECT * FROM TABLE1". I need to get this value from the SqlDataSource into the Sub Page_Load. I don't want to bind to any gridviews, repeaters etc. I just want to get the number of rows/results from the SqlDataSource.Is there a way of doing this?Thanks
View 4 Replies View RelatedI would like to add a field to a query that returns an ordinal number indicating which row of the results it is. Anybuddy know how?
eg. 1,2,3,4,5,6,7....
Thanks,
Carl
I would like to get the top 5000 records from a union. The oringinal query is
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
I thought that the I could do:
SELECT TOP 5000 * FROM
(
SELECT * from tbl_A WHERE fld_Id = 0
UNION ALL
SELECT * from tbl_B where fld_Id = 0
}
This gives me a syntax error that I cannot figure out.
Any ideas?
I am doing some SELECT queries on my database through ASP, but for example, I only want to return the 50 most recent entries that match the criteria. Is there any easy way to limit the number of results returned?
View 1 Replies View RelatedI have a query that pulls records from a table based on whether is matches one or more of several criteria. I'd like to be able to order the results of this query by the number of conditions a particular row met. So a row that met 3 conditions would rank about a row that only met 2. So far I haven't been able to dent this with any attempt so I'm open to any suggestions.
View 5 Replies View RelatedIn other SQL programs I have seen the use of Limit to tell it how many rows to return.
Is there an equivalent in MS-SQL that will let me do a quick Select clause and tell it how many rows to return.
In other words if I just wanted to see the first 10 rows what would I add to Select * from tableA
Thanks!
Iam using front page to dispalay my results.
At the bottom it shows me 1/10 i.e 1st page of 10 pages.
but what do i do if i want it to be shown as 1-10 out of 100 (if each page contains 10 results).
or it would be really good if i get count of both no. of recors as well as no. of pages.
Hello,
I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:
The user can make a selection from the categories listed below:
Geographic Area
Time Period
Topic
Record Type
If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:
Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)
Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.
Thanks
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
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 RelatedI have an Excel/ vba module to update a SQL database. One of the data is numeric. If the data is a positive number, there is no problem, but if the number (zscore) in Excel is negative an error pops up: Error converting datatype varchar to numeric.
zscore = Range("J" & mytel99)
rc.Open "INSERT INTO dbo.QHSE_3rdline_history (RvA_Nr, RvA_Letter, Afdeling, Datum, Organisator, NummerRO, Eenheid, ALC, RO, Interpretatie) " _
& "VALUES ('" & rva1 & "', '" & rva2 & "', '" & Range("B" & mytel99) & "', '" & datum & "', '" & Range("B1") & "', '" & Range("B2") & "', '" & Range("G" & mytel99) & "', '" & Range("H" & mytel99) & "', '" & Range("I" & mytel99) & "', '" & Range("K" & mytel99) & "')", con
rc.Open "UPDATE dbo.QHSE_3rdline_history SET [Matrix] = '" & Range("C" & mytel99) & "', [Component] = '" & Range("D" & mytel99) & "', [Zscore] = '" &
[code]....
how to get negative data in a numeric SQL table?
I've got this issue with a query in SSIS. From a table in SQL Server I'm getting over 25000 different identifiers. These identifier are associated to many values in a table in one Oracle Database. This is the schema that I have implemented for doing this.
The problem is that some days the identifiers can be over 45000, and at this point perform a loop for every one is not the best solution (It can take to much time to get the result). Previously I have performed another query where from the SQL statement.
I am creating and sending a unique row with all the values concatenated and then I have recover this unique string from an object and use it to create the query in the ODBC Source that invoke the table in Oracle: something like this:
'Select * from Oracle_table' + @string_values
with @string_values = 'where value in (........)'. It works good because the number of values is small enough to be used, like 250. But in this case I can not use this approach because the number is really big and obviously the DBA of Oracle is going to cancel the query.
So I wonder, how can I iterate over the object getting only a few number of values everytime, something like 300 or maximum 500, to avoid the cancellation of the query but at the same time doing the minimum number of loops.
Hello!
is it possible to have the count(*) to display 0 when there is no matching hits for that n_id?
my query looks like this but only displays the n_id and it's respective count(*) when count(*) is more than 0...
select n_id, count(*) from tblTable
where nSomething > nSomethingElse AND nSomething IS NOT NULL
group by n_id
any idaes?
This routine works in most cases, but fails when a bad date is enteredsuch as:19910631 -- there is no June 31st.Instead of ignoring the bad date the entire DTS job fails. Obviouslythis is something that should be validated at data entry, butunfortunately the only control I have is when appending to the tablewith these data quirks. Any suggestions appreciated!!!'************************************************* *********************' Visual Basic Transformation Script' Copy each source column to the' destination column'************************************************* ***********************Function Main()'DTSDestination("Col002") = DTSSource("Col002")if DTSSource("Col002") = "99999999" or DTSSource("Col002") =Null thenMain = DTSTransforStat_SkipRowelseDTSDestination("Col002") = mid(DTSSource("Col002"),1,4) & "/"&mid(DTSSource("Col002"),5,2) & "/" & mid(DTSSource("Col002"),7,2)End ifMain = DTSTransformStat_OKEnd FunctionRBollinger
View 3 Replies View RelatedHi.
This is my first attempt at a using stored procedures and I'm a bit confused.
I'm trying to follow as many best practices as I can to improve speed, security and scalability. However, I can't find a solution to what I think should be a simple problem.
I have a search page where users enter the criteria of properties they are interested in (bedrooms, price etc...). That takes them to a results page where the properties are displayed.
The problem is that I want the amount of times each property has been shown on the results page to be tracked so the property owner gets statistics.
The property details are all held in a single table along with the amount of times each property has be shown:
Table Name: zk_Property_USA
ID
INT
User_ID
INT
Property_Type
TINYINT
Market_Status
TINYINT
Price
INT
Bedrooms
TINYINT
Address_State
VARCHAR
Address_Location
VARCHAR
Property_Description
VARCHAR
Searched
INT
0
Contacted
INT
0
I'm trying to find a way to SELECT all the property details to be returned to my results page and UPDATE the "Searched" field by 1 without re-scanning the table for the UPDATE.
Is there a way to update "Searched" at the time when the record is chosen to be a result? I am using SQL Server 2005 and Visual Basic ASP.NET 2.0.
Many Thanks
In any asp.net application, whats the simplest way to monitor how many times a page hits the database (opens and closes a connection)?
View 1 Replies View RelatedIn a Lookup component I've defined a SQL query which returns a sorted resultset. For each Lookup component input row I want to have a single output row. Problem is that for each input row there is possibility of multiple matches in SQL query resultset. From all of the possible multiple hits I want only the first one to be returned, and if no match is found then no output row. How to implement this?
View 10 Replies View RelatedI am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
Using MSSQL (pre 2005):
I have a Link table:
int id (primary key/identity)
varchar(50) linkName
varchar(255) linkHref
//some other stuff
and my Hits table:
int id (primary key/identity)
int linkId (foreign key to Link)
datetime dateCreated
//some other stuff
Hits gets an insert whenever a link is clicked. (All this works just fine)
I'm trying to create a report that shows each link by its name and href, a counter and the last date each link was visited. What I currently have is an accurate listing for those that have been clicked on, but it does not show anything for links that haven't been clicked on. Any suggestions as to how I can modify the following SQL to return "0" and "never" (or DBNULL) if no entries are found in Hits that have the same id? Or do I have to do this in a couple queries?
SELECT COUNT(h.id) as counter, MAX(h.dateCreated) as lastVisited,
l.id as id, l.linkName as linkName, l.linkHref as linkHref
FROM Link as l INNER JOIN Hits as h ON l.id=h.moduleId
GROUP BY l.linkName, l.id, l.linkHref
Hello,
i am making a Fulltextsearch on MS SQL Server 2005 (indexed, with "Contains").
Because of performance reasons i am only showing the first 200 rows mssql finds ("select top 200...:").
Is there any possibility to get the estimated totalnumber of all rows? i have heard something that is possible to get this in mssql-server. The server then estimates how many rows with that searchword could be in the whole database. google i.e. makes the same thing....
is that true? what do i have to do to get this?
greetings and thx
cpt.oneeye
I'd like to capture the avg. # of user logins and # db hits per a 5 interval for a weeks time. I'm guessing there are sys tables containing this info. and by using temporary tables and/or creating/modifying SPROCS this info. can be retrieved. If I'm on the right track, a little direction would be very appreciated. If I'm not on track, please assist this rookie dba.
Thank you,
Eoin
I am serving ad-units. In each ad-unit I show somewhere between 3 and 10 article headline. I track the headline impressions to get an idea of the headline click through rate. I save the output from the stats process in another table. I am currently evaluating the stats every hour, and then truncating the table every night at midnight. The problem is that I get lots of impressions and the database gets bogged down evaluating the data such as...
SELECT COUNT FROM articleimpressions WHERE articleid = x
But the issue isn't the reporting of the data...so much as it's the capture. I had to add caching on the ad-server because the database couldn't handle the number of inserts.
I thought about parsing the web server log files the next day, but the file sizes seem to be too large, and I can't process them all in one day. (At least not on the hardware that I am using.)
I've thought about splitting log files by hour, but was wondering if there may be a more "native" solution within SQL Server? Maybe a trigger, and/or multi-threaded SP that fires and forgets an insert statement to a linked server. But performance is the key here.
Thoughts?
hi . would greatly appreciate a clever way to make an internal "counter" on an sql table that contains 6000 articles.(one in each row). these are being accessed from a web page and i would like to know which are the most read ones. so i thought of adding a column that would in some way count each time that the row is being accessed. is there a way for this to be done?? please be detailed since i am quite new to all this.
thanks beforehands.
Hello friends
I am new to SQL database design.I am having a table name customerletter_master in which all details of customer letters are saved.At a time i.e in single second around 200- 400 entry are made in customerletter_master table. Hits to customerletter_master table are more.
Following fields are used in customerletter_master – CID (autogen number), letterno(primary key),consignee, consignor, letterstatus1, letterstatus2, letterstatus3, POD, and some more fields. When letter passes to different stages letterstatus1 is filled to yes and then letterstatus2 and letterstatus3 are filled according to passage of letter.
And at same time many user can accesses the customerletter_master table to search any letter according to letterno. Therefore customerletter_master is used to enter data and to search data at same time and there can be more than 200-400 users doing add and search records to and from customerletter_master
how should i design this table. What should i use to enter and search record and minimize the table hits made by the user at same time. Should i use store procedures or any other method to add and search record. Plz help me out by giving some example.
I'm trying to piece together some code on how to find members of our organization who have visited our website.
View 2 Replies View RelatedI was curious to know if it the amount of data sent to the sql server mattered.
I am working on a web application and I have three stored procedures that most likely will be called one after the other. Each procedure accepts at least 4 parameters. Instead if I create one stored procedure, then I will be passing at least 12 parameters. Some of the parameters could be quite bulky(at least 1000 characters).
So which one is better, 1 stored procedure with 12 parameters or 3 stored procedures with 4 parameters each called one after the other.
Thanks
I am trying to script a case when to achieve the following.
I have a table of measures with certain threshold. The threshold direction can either be > or < so I want to create a field that shows if the measure hits that threshold or not to be later picked up in SSRS. So a nested case when?
CASE WHEN M.[Threshold Direction] = '>' THEN
CASE WHEN A.[Value] > M.[Threshold] THEN 'GREEN'
CASE WHEN A.[Value] < M.[Threshold] THEN 'RED'
ELSE ''
END END END AS 'Condition'Is this at all possible?
hi, like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right? so, is there something that i can use to hold those records so that i can do the delete and update just on those records and don't need to query twice? or is there a way to do that in one go ?thanks in advance!
View 1 Replies View RelatedI have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?