problem with order by in query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem with order by in query

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-16 : 21:16:39

Hello in the above query i have the problem that if i order by @searchword then i get the rows correctly but if i add date then @searchword is ignored and the ordering is done by date first.
Any help?Thanks.

declare @startRowIndex int
--maximurows = to page size
declare @maximumRows int
set @startRowIndex=1
set  @maximumRows =5
DECLARE @SearchWord nvarchar(50);
SET @SearchWord ='starting words';



select rownum,id,headertext,innertext,url,photo,datec
From
(select id,headertext,innertext,url,photo,datec,
 ROW_NUMBER() OVER(ORDER BY @SearchWord asc,datec desc) as rownum
From news n where FREETEXT(*, @SearchWord) ) as newsinfo
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:59:26

it depends on how you want ordering sequence to go
if you need to order by date first it should be

ORDER BY datec desc,@SearchWord asc

the current way you're having it, ordering is done first on basis of @searchword variable and then by the date values

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-17 : 18:45:33

Hi.Yes that is exactly how i want it ordered but i believe the problem may be on how FREETEXT is handling the data on Greek language.I tested everything with English words and i get the results fine.Sometimes in Greek language i get the opposite ordering.I've read that Microsoft is not currently full supporting this in Greek(meaning freetext will not have a dictionary for relevant words in Greek) and it seems that freetext itself has some minor problems supporting Greek language.Anyhow it works in 19 out of 20 tests i conducted so i suppose it's fine for now,until Microsoft support this in full.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 00:39:08

quote:
Originally posted by sapator

Hi.Yes that is exactly how i want it ordered but i believe the problem may be on how FREETEXT is handling the data on Greek language.I tested everything with English words and i get the results fine.Sometimes in Greek language i get the opposite ordering.I've read that Microsoft is not currently full supporting this in Greek(meaning freetext will not have a dictionary for relevant words in Greek) and it seems that freetext itself has some minor problems supporting Greek language.Anyhow it works in 19 out of 20 tests i conducted so i suppose it's fine for now,until Microsoft support this in full.
Thanks.

the order also depends on collation setting. If you're using a collation setting which is not in accordance with Greek language then it can even affect your order of retrieval

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-18 : 13:56:50

Hi.I use Greek_CI_AI .
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 23:47:46

ok..then that should not be a problem
did you try using case sensitive alternative also? if thats what you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2011-12-19 : 00:05:56

No,you see in Greek we have some symbols (called "toni") that goes up from the vowels (like the "'" spot in "i") and i don't want people to have to include them on their writing.Also i don't want the search to care for capital letters.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources