Hi, I am writing a small search engine.
There are two tables. The first one holds the search engine main index, the second one is link table.
I have the following query that retrieves results. I would like to sort the results by:
dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query?
I'm using MSSQL 2005. Thanks.
PS. Function OCCURS2 returns number of occurrences of one string in other.
Code:
select
id as Id,
uri as ElementUri,
size as Size,
modified_date as ModifiedDate,
title as Title,
text as Text,
dbo.OCCURS2(LOWER(:query),Title) as TitleOcc,
dbo.OCCURS2(LOWER(:query),Text) as BodyOcc
FROM se_index
WHERE (title LIKE :query) OR
(text LIKE :query) OR
(id IN
(SELECT se_links.target_index_id
FROM se_links INNER JOIN
se_index AS se_index_1 ON
se_links.target_index_id = se_index_1.id AND
se_links.anchor LIKE :query))
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
Trying to understand complex joins (or complex to me, at least).I have a series of tables which have data I want to join in order to get a useful report. Most of the joins work fine, e.g.:
SELECT DISTINCT * From Documents INNER JOIN LookUpTable ON LookUpTable.ObjectId = Documents.LastVersionOwnerId
This give me the name of the person who owns the most recent version of a document, as the LookUpTable table maps userid numbers to names.But I also want to correlate the OriginalOwnerId column from the same Document table with its LookUpTable counterpart. I can't figure out how to get that second join, for the same tables, to work.
If I have three large tables to join together should I join the two that I know will cut the number of rows down a lot first and then join the 3rd table or does it make no difference (if I join the first and 3rd - which I know will be a large result set and then join the 2nd).
I have an problem with the order of the results after a join.
My first query works fine and the order of field Name ist correct.
Select * FROM (SELECT * FROM dtree A1 WHERE A1.Subtype=31356 AND A1.DataID IN (select DataID from dtreeancestors where AncestorID=9940974)) t
When I do a join the order of the left table changes
Select * FROM (SELECT * FROM dtree A1 WHERE A1.Subtype=31356 AND A1.DataID IN (select DataID from dtreeancestors where AncestorID=9940974)) t, llattrdata A4 WHERE t.DataID = A4.ID
How can I do a join and keep the order of the left table?
Hi all, I faced a problem, I have two tables - part and partmaster part : part_no, part_qty (no key) partmaster : part_no, part_description (primary key : part_no )
I want to select table part.* and partmaster.part_description.
(run on mssql 2k) select a.*, b.part_description from part a, partmaster b where a.part_no *= b.part_no
I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.
Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.
can anyone tell me the reason?
Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq. It does work!
In Outer join, I would like to add the outer columns that don't exist in the right table for each order number. So currently the columns that don't exist in the right table only appear once for the entire set. How can I go about adding PCity, PState to each order group, so that PCity and PState would be added as null rows to each group of orders?
if OBJECT_ID('tempdb..#left_table') is not null drop table #left_table; if OBJECT_ID('tempdb..#right_table') is not null drop table #right_table; create table #left_table
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeID sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeID AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeID AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeID AS SickBalance
and the table should be shown as below after executing the query
Does column order really matter for Query Optimizer to pick index.Case 1: Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?Statement 1:SELECT * FROM CUSTOMER WHERE FirstName = 'John' and LastName ='Smith'Statement 2:SELECT * FROM CUSTOMER WHERE LastName ='Smith' and FirstName = 'John' Will both statement 1 and 2 use the composite index or only statement 1?Case 2:Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?I read couple book and some books say column order matter but some say no. Which one should I go with? I'm kind of confused.
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS 2 ( 3 SELECT 4 Id, 5 Name, 6 Created, 7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers, 8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber' 9 FROM Projects p 10 ) 11 SELECT * 12 FROM ProjectList 13 WHERE RowNumber BETWEEN 50 AND 60;
This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error: Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'. I read this in the documentation: When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.
I have a SELECT statement in an SP that selects 10 fields, however, i want to be able to pass a variable to the SP to determine which field to ORDER BY.
Is there a way to do this ?
I've tried passing in one of the field names to a variable and then doing ORDER BY @OrderByThisColumn ...nope. I've tried SETting a variable to the above @OrderByThisColumn ...nope.
SELECT H.Fund_Man as Holders, H.Shares as SharesHeld, H.Share_Pric * H.Shares as Value, H.Pcent as SharesOutstanding, H.Shares - H.Shares as ShareChange, C.Reg_Date as ReportDate, 'Register' as Source, ((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent, H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight, (H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit
FROM Citywatch_Company C Inner Join Citywatch_Holders H On C.Epic = H.Epic Inner Join Citywatch_Sector S On H.Sector = S.Sec_Code WHERE C.Epic = @CompanyCode
Hello,Using SQL 2005. Columns:ID, int (PK, auto-increment by 1)WorkHours, intName, varchar(100)I can't seem to get the following query to work. I want to return allNames and the sum of ALL work hours, in each row and order by eachINDIVIDUAL work hour:SELECT Name, SUM(WorkHours) as hFROM EmployersORDER BY WorkHours DESCIt seems that putting WorkHours in but the aggregate function and theORDER BY clause creates a problem.Thank you for your help!
I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?
Hi,I created a composite index (lastname, firstname). I know the followingqueries will use this index:WHERE lastname = ...WHERE lastname = ... AND firstname = ...Also this won't use the index:WHERE firstname = ...But how about: WHERE firstname = .. AND lastname = ...And why?Thanks a lot,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table.TIALaurence Breeze
I have two tables, one is called (questions), the second one (answers).
questions columns are (ID,questionTitle)
answers columns are (ID,questionID,answer, answerDate)
I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.
Is there any truth to this: the placement of fields in a table relates to field access speed. So, frequently accessed fields should be placed in the beginning of the table while fields infrequently used can be placed toward the end.
