Similar Posts like functionality using MS SQL Server?
Something like this might work, a kind of ranking system. You would probably have to split the string in your application to build a SQL string, but I have used similar to build an effective site search.
Select
Top 10
ArticleID,
ArticleTitle,
ArticleContent
From
Articles
Order By
(Case When ArticleTitle = 'Article Title' Then 1 Else 0 End) Desc,
(Case When ArticleTitle = 'Article' Then 1 Else 0 End) Desc,
(Case When ArticleTitle = 'Title' Then 1 Else 0 End) Desc,
(Case When Soundex('Article Title') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,
(Case When Soundex('Article') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,
(Case When Soundex('Title') = Soundex(ArticleTitle) Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%Title%', ArticleTitle) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%', ArticleTitle) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Title%', ArticleTitle) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%Title%', ArticleContent) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Article%', ArticleContent) > 0 Then 1 Else 0 End) Desc,
(Case When PatIndex('%Title%', ArticleContent) > 0 Then 1 Else 0 End) Desc
You can then add/remove case statements from the order by clause to improve the list based on your data.
What is the SQL used to do a search similar to Related Questions on Stackoverflow
After enabling Full Text search on my SQL 2005 server, I am using the following stored procedure to search for text.
ALTER PROCEDURE [dbo].[GetSimilarIssues]
(
@InputSearch varchar(255)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SearchText varchar(500);
SELECT @SearchText = '"' + @InputSearch + '*"'
SELECT PostId, Summary, [Description],
Created
FROM Issue
WHERE FREETEXT (Summary, @SearchText);
END
Implement paging (skip / take) functionality with this query
In SQL Server 2012 it is very very easy
SELECT col1, col2, ...
FROM ...
WHERE ...
ORDER BY -- this is a MUST there must be ORDER BY statement
-- the paging comes here
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
If we want to skip ORDER BY we can use
SELECT col1, col2, ...
...
ORDER BY CURRENT_TIMESTAMP
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
(I'd rather mark that as a hack - but it's used, e.g. by NHibernate. To use a wisely picked up column as ORDER BY is preferred way)
to answer the question:
--SQL SERVER 2012
SELECT PostId FROM
( SELECT PostId, MAX (Datemade) as LastDate
from dbForumEntry
group by PostId
) SubQueryAlias
order by LastDate desc
OFFSET 10 ROWS -- skip 10 rows
FETCH NEXT 10 ROWS ONLY; -- take 10 rows
New key words offset
and fetch next
(just following SQL standards) were introduced.
But I guess, that you are not using SQL Server 2012, right? In previous version it is a bit (little bit) difficult. Here is comparison and examples for all SQL server versions: here
So, this could work in SQL Server 2008:
-- SQL SERVER 2008
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 10,@End = 20;
;WITH PostCTE AS
( SELECT PostId, MAX (Datemade) as LastDate
,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
from dbForumEntry
group by PostId
)
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY PostId
Creating a related or similar posts using PHP & MySQL
Using the MySQL Full Text search MATCH (col1,col2,...) AGAINST (expr [search_modifier])
thing.
Let's say your table is articles
and you need to find related posts about a title of current post. Do it like this:
SELECT *, MATCH(title, body) AGAINST('$CurrentPostTitle') AS score
FROM articles
WHERE MATCH(title, body) AGAINST('$CurrentPostTitle')
ORDER BY score DESC LIMIT 5
This will give you top 5 related posts.
But first remember to enabled Full Text search for that table's columns, by running this query:
ALTER TABLE articles ADD FULLTEXT (title, body);
[EDIT]: Why to not use LIKE
: Clarification to OP:
Because it will not give correct results. Let's say you current title is "Music of 1980" and you want related posts on that. Now, if you use LIKE
then only the posts containing EXACTLY the sequence of words "Music of 1980" will appear. However, if you use MATCH ... AGAINST
, then posts that contain Music OR 1980 will appear. Also, the posts that contain both Music and 1980 will appear on Top because it gives a SCORE
to each results and we are sorting by that score.I hope that's clear.
[EDIT]: 2:
If you have categories, you can add AND Category = '$CurrentCategory'
in the SQL query where clause
to get more specific results.
[EDIT]: 3: OP can't use Full text:
If you can not use Full Text (for some reason), you can just show 5 random posts from the same category. As they are in same category, they are somehow related at least:
SELECT *
FROM articles
WHERE Category = '$CurrentCategory'
LIMIT 5
Edited Syntax: Changed LIMTI to LIMIT in MySQL Code
NodeJS backend post request posts data to the SQL Server table as 'NULL' values
All this time the issues was not in my code, actually it was the way I tried to post data using an object inside an array. All I did was removing the array brackets in Postman and there you go, the data is showing in the SQL Server table.
How to list all jobs which operate with specified database object?
There is no built-in way to find indirect dependencies. You can check which jobs directly call a certain stored procedure, but no way to see which tables or other objects get used by that stored procedure unless you check the direct dependencies of that stored procedure.
So if you are looking for a specific table, you can find objects that reference it directly, but not objects that reference THOSE objects (indirect dependency).
You would either have to write your own script or search around and see if someone else has written one.
sql server - inserting rank column in SP table-parameter
I have tried using IDENTITY on a column, but it seems that the DataTable class that I used didn't allow inserting rows with less columns than the number of columns the table had (so that I could not omit the IDENTITY column when inserting data and let it auto-increment).
What I did instead, was providing the rank column values from C# code and order by that column in my SP.
Not the best solution that I could imagine, but at least it works.
Running a process that interacts with SQL Server from a Windows Service - alternative to SQL Server agent jobs
The right way to achieve a reasonable security level is:
- create a new user that will run the Windows Service (you can create this user in Control Panel)
- create a new login for this user in SQL Server (using Integrated Security)
- give the necessary, but minimum, permissions to this user in SQL Server
To implement a service you must be aware of a few things:
- if you use WCF (or any other service stack), you need a thread for listening the requests and a different thread to run the processes in SQL server. If not, the subsequent requests will probably time out. (You can launch new threads for each task, or implement a queue to avoid overloading the server).
- be extremely careful with error (exception) handling. If an unhandled exception is thrown your service will stop, until someone starts it again
- it's harder to implement the UDF solution, becasuse you need to modify SQL Server security settings to allow an UDF to communicate with the "outer world"
- you should include some kind of logging in your service, so that you can test it and verify it's working correctly.
It's not to hard to make an executable that can be run as console app as well as as a service. If you do so you can run it from the Visual Studio debugger, or stand alone, and log in the console (Console.Write...(...)
), to see what's going on.
When should I use CROSS APPLY over INNER JOIN?
Can anyone give me a good example of when CROSS APPLY makes a difference in those cases where INNER JOIN will work as well?
See the article in my blog for detailed performance comparison:
INNER JOIN
vs.CROSS APPLY
CROSS APPLY
works better on things that have no simple JOIN
condition.
This one selects 3
last records from t2
for each record from t1
:
SELECT t1.*, t2o.*
FROM t1
CROSS APPLY
(
SELECT TOP 3 *
FROM t2
WHERE t2.t1_id = t1.id
ORDER BY
t2.rank DESC
) t2o
It cannot be easily formulated with an INNER JOIN
condition.
You could probably do something like that using CTE
's and window function:
WITH t2o AS
(
SELECT t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
FROM t2
)
SELECT t1.*, t2o.*
FROM t1
INNER JOIN
t2o
ON t2o.t1_id = t1.id
AND t2o.rn <= 3
, but this is less readable and probably less efficient.
Update:
Just checked.
master
is a table of about 20,000,000
records with a PRIMARY KEY
on id
.
This query:
WITH q AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
FROM master
),
t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
JOIN q
ON q.rn <= t.id
runs for almost 30
seconds, while this one:
WITH t AS
(
SELECT 1 AS id
UNION ALL
SELECT 2
)
SELECT *
FROM t
CROSS APPLY
(
SELECT TOP (t.id) m.*
FROM master m
ORDER BY
id
) q
is instant.
Related Topics
Sync Between SQL Server and MySQL Server
SQL Question: Does the Order of the Where Clause Make a Difference
Postgresql Update Multiple Tables in Single Query
Paging with Oracle and SQL Server and Generic Paging Method
There Are No Primary or Candidate Keys in the Referenced Table
Why Can't I Group by 1 When It's Ok to Order by 1
T-SQL Select Get All Months Within a Range of Years
What Is the Default SQL Result Sort Order with 'Select *'
Where Clause Using Values That Could Be Null
Using Patindex to Find Varying Length Patterns in T-Sql
Row_Number() Over Not Fast Enough with Large Result Set, Any Good Solution
Get the Number of Affected Rows in a MySQL Update Statement
Rails, Ransack: How to Search Habtm Relationship for "All" Matches Instead of "Any"
Using Variables for Asc and Desc in Order By
How to Deep Copy a Set of Data, and Change Fk References to Point to All the Copies