MS SQL Server :: How To Order Search Results By Relevance?

Ranked / Relevance Search Results

Sep 19, 2006

Hi guys,As the title shows, i would like to learn how to return ranked results, and be able to order by relevance/rank. I am currently using SQL2000, though the final site may be going live with SQL2005. The data i am searching against is in tables, not Indexed files.I have no idea where to start......Does it have somthing to do with the amount of times the keywords appear? any help would be greatRegardsDarren

View 3 Replies View Related

SQL Search :: Can't Get Expected Results With Contains And Full Text Search?

Nov 1, 2015

I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:

123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999

I am trying this query:

select * from Mytable where
contains(MyColumn, '123.345.')

I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:

select * from MyTable where
MyCOlumn like '123.345.%';

View 12 Replies View Related

SQL To Order Results In Predefined Order

Mar 27, 2008

I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?

View 4 Replies View Related

Specify Order For Select Results, Order By: Help!

Nov 17, 2006

Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View 1 Replies View Related

Results In Order

Jun 13, 2007

I have the following query and I am wanting to get the results to be in order. Right now, it shows me the results by date, but the dates are out of order. How can I get it to give me the results by date in date order???

SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007'
and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0)
UNION
SELECT DISTINCT MDN, dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE CallDate >= '02/19/2007' and calldate < '03/19/2007'
and NOT (Left(Endpoint,3) IN ('011')
or (Left(Endpoint,4) IN ('1340','1876','1868','1809',
'1246','1242','1780','1403',
'1250','1604','1807','1519',
'1204','1506','1709','1867',
'1902','1705','1613','1416',
'1905','1902','1514','1450',
'1418','1819','1306','1867')))
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group By MDN, dateadd(day, datediff(day, 0, CallDate), 0)

View 5 Replies View Related

Search Within Search Results?

Mar 14, 2007

Hi guys
I need to create an "advanced search" which will allow the user to narrow down his results.
Ideally I'd want him/her to use the same search criteria form for each iteration, with a checkbox called "Search within results" type of thing.
Now what I was wondering if there was any existing literature on how to effectively do this. I have tried doing it just through SQL Statements but they are becoming very messy and large.
Is it possible to do this by searching the initial dataset, returning dataset #2 and then if a 3rd "search within results" is done apply the search against dataset #2 and return dataset #3 etc?
Many Thanks
John

View 2 Replies View Related

How To Order Results From DateName?

Mar 13, 2008

HiI want to get the dateName of everything but I don't know how to sort them now. Like I have this:DateName(month,TimeDateStamp) As TimeDateStampWhat gets the dateName now I want to sort these so that they are in order.How do I do this?

View 3 Replies View Related

Order / Rank Results

Feb 8, 2006

Hi,

i want to create a report so that a list of the top 30 records are returned to the report user. In the report i want to have the records position in the list shown (ie the first row should have 1. and the second should be 2. right on down to the 30th having 30.)

how do i achieve this please?

many thanks
FatherJack

View 1 Replies View Related

Top + Order By = Strange Results

Mar 14, 2008

Hi there,I'm a little bit confused here. I the TOP 1 function with ORDER BY DESC to get the last id in my table but it doesn't seems to work.Here's an example:SELECT TOP 1 ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID DESCSELECT ID FROM Worksheet WHERE Style='302' AND Notes='Automatically created from m0851System.' AND Title='STD COST UPDATED FORM m0851System' ORDER BY ID ASCSo basically the first SELECT should return the last id value of the second query but it doesn't.The first query gives me that: 60721And the second one gives me that:60680606816068360684606856068660718607196072060721610506112261124So as you can see my TOP 1 ID ORDER BY ID DESC should gave me this result: 61124Am I missing something or... ?Please help me this is aleready in function so I have to fix it ASAP.Thank you, Regards,OR-THO

View 5 Replies View Related

Select Like And Order Results

Apr 25, 2008

Hello there,

I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table:
1- the exact match for the search string come first from the two table.
2- and the partial match comes last after the exact match.

this is my DDL for the two tables :

USE [Northwind]
GO
/****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

second table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
[MemberID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

and this my search query I have it in a stored Proc.

select *

from

(

SELECT PersonID, Type, Name, email

FROM Person
WHERE (Name LIKE '%'@Name'%')
union all

SELECT PersonID, Type, Name, email

From Members
WHERE (Name Like '%'@Name'%' )

) Y

Order by

Case[Name] when @Name Then 1 Else 2 End,

Case[Name] when 'm' Then 1 Else 2 End

Thank you for your time
Sms

View 5 Replies View Related

Full Text Search Order By Help

Mar 19, 2008

Hi

I am building a search for my application and want to search a table in my database. The table has a Description field

I want to search the Description field and output the results where this search is found. However I want order my results by where the number of occurrences of that word is found the most.

So for example if a user types in 'red car' in my search will priorities this by the number of times 'red car' was used in my description.

Many thanks in advance

View 1 Replies View Related

No Search Results When Using A StroredProcedure

Sep 18, 2007

Hello! I am using this code to search the database but for some reason it's not liking the parameter I put there. Here is my code that works great with no parameters:
protected void getBusinessInfo()    {        DataSet ds;        ds = SqlHelper.ExecuteDataset(connection, "spBusinessInfoSearch");        gvSearch.DataSource = ds;        gvSearch.DataBind();
    }
protected void btnSearch_Click(object sender, EventArgs e)    {                 getBusinessInfo(txtName.Text);    }
Here is what I put in the stored procedure:
ALTER PROCEDURE dbo.spBusinessInfoSearch

ASSELECT * from vwUserprofileInfo where DisplayName like '%Eric%'

RETURN
Now, when I used the same code with paramers nothing is returned.
protected void getBusinessInfo(string param1)    {        DataSet ds;        ds = SqlHelper.ExecuteDataset(connection, "spBusinessInfoSearch", new SqlParameter("@DisplayName", param1));        gvSearch.DataSource = ds;        gvSearch.DataBind();
    }
protected void btnSearch_Click(object sender, EventArgs e)    {                 getBusinessInfo(txtName.Text);    }

View 7 Replies View Related

SQL Search Results Not Working???

Dec 10, 2005

Can anyone suggest whey I dont get any results with this search?

SQL reads:

SELECT SupplierName, Location, ShortDescription
FROM Query1
WHERE 'TimberSpecies' LIKE '%MMColParam%' AND 'CategoryTable' LIKE '%MMColParam2%' AND 'Location' LIKE '%MMColParam3%'

MMColParam 1 Request.Form("keywordSearch")
MMColParam2 2 Request.Form("location")
MMColParam3 3 Request.Form("category")

Mally.

View 4 Replies View Related

No Results Found For My Search!

Aug 24, 2005

Dear all, I have illustared with code and sample output data my requestin thsi post. I simply was expecting some results from my search "amdsocket a 32 bit cache 512 dell" that includes a logical AND for all thewords in that search.Since i assume that any word might be an item_name, item_key oritem_value, i included all in the search.Can any one tell me why i get 0 results?The sample output of my data should look like this:item_id item_nameitem_keyitem_value--------------------------------------------------1Gefore MX 440Size64 MB1Gefore MX 440Architecture64 Bit1Gefore MX 440AGP8x1Gefore MX 440ChipsetNvidia1Gefore MX 440VendorAsus2AMD 3200+ClassSocket A2AMD 3200+Speed2 GHz2AMD 3200+Architecture32 Bit2AMD 3200+Level 2 Cache512 KB2AMD 3200+VendorAMD3Dell P780Geometry17 Inch3Dell P780Screen TypeFlat3Dell P780Frequency60 Hz3Dell P780VendorDellHere is my scenario:create table item_table (item_id int identity (1,1) not null primarykey, item_name varchar (50) not null)gocreate table details_table (item_id int not null, item_key varchar(50), item_value varchar (50))goalter table details_table add foreign key (item_id) referencesitem_tablegoinsert into item_table values ('Gefore MX 440')goinsert into item_table values ('AMD 3200+')goinsert into item_table values ('Dell P780')goinsert into details_table values (1,'Size', '64 MB')goinsert into details_table values (1,'Architecture', '64 Bit')goinsert into details_table values (1,'AGP', '8x')goinsert into details_table values (1,'Chipset', 'Nvidia')goinsert into details_table values (1,'Vendor', 'Asus')goinsert into details_table values (2,'Class', 'Socket A')goinsert into details_table values (2,'Speed', '2 GHz')goinsert into details_table values (2,'Architecture', '32 Bit')goinsert into details_table values (2,'Level 2 Cache', '512 KB')goinsert into details_table values (2,'Vendor', 'AMD')goinsert into details_table values (3,'Geometry', '17 Inch')goinsert into details_table values (3,'Screen Type', 'Flat')goinsert into details_table values (3,'Frequency', '60 Hz')goinsert into details_table values (3,'Vendor', 'Dell')gocreate view all_view asselect top 100 percent i.item_id, i.item_name, d.item_key, d.item_valuefrom item_table as i left outer join details_table as don i.item_id = d.item_idorder by i.item_id, i.item_name, d.item_key, d.item_valuego-- the complete search is "amd socket a 32 bit cache 512 dell"declare @search_key1 as varchar (50)declare @search_key2 as varchar (50)declare @search_key3 as varchar (50)declare @search_key4 as varchar (50)declare @search_key5 as varchar (50)declare @search_key6 as varchar (50)set @search_key1 = 'amd'set @search_key2 = 'socket a'set @search_key3 = '32 bit'set @search_key4 = 'cache'set @search_key5 = '512'set @search_key6 = 'dell'select distinct item_idfrom all_viewwhere((item_name like '%' + @search_key1 + '%') or (item_key like '%' +@search_key1 + '%') or (item_value like '%' + @search_key1 + '%'))and((item_name like '%' + @search_key2 + '%') or (item_key like '%' +@search_key2 + '%') or (item_value like '%' + @search_key2 + '%'))and((item_name like '%' + @search_key3 + '%') or (item_key like '%' +@search_key3 + '%') or (item_value like '%' + @search_key3 + '%'))and((item_name like '%' + @search_key4 + '%') or (item_key like '%' +@search_key4 + '%') or (item_value like '%' + @search_key4 + '%'))and((item_name like '%' + @search_key5 + '%') or (item_key like '%' +@search_key5 + '%') or (item_value like '%' + @search_key5 + '%'))and((item_name like '%' + @search_key6 + '%') or (item_key like '%' +@search_key6 + '%') or (item_value like '%' + @search_key6 + '%'))go----Best regards

View 20 Replies View Related

Help With Search Results Query

Jul 20, 2005

I need some help with a query. I have a table with inventory that Ineed to allow customer searches on. Based on their search criteria, apreference level is calculated; the higher the preference level, thehigher on the order on the search results.The hard part is when the results are supposed to be limited to amaximum number of stores and items. Let's say that they only want tosee 3 stores and a max of 5 items per store. What needs to bereturned is the 3 stores with the best Preference and the 5 best itemsat each store.Create Table Inventory( StoreId int, ItemId int, Preference int )

View 1 Replies View Related

Order Results By Date Not Working

Oct 3, 2006

hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:

oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16

the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.

also, can you not search here any more? i keep getting timeout errors.

View 7 Replies View Related

Concatenate Results In Specific Order

Jul 24, 2013

I am trying to Concatenate resulting classification names (X_DSC_CD_ABR below) in the following order (if the classification exists), separated by '/': WC/STD/LTD/FMLA/State/Military/Paid/Corporate/PFL/Other

Examples:
a. STD/FMLA
b. STD/LTD/FMLA/Other

View 2 Replies View Related

Ordering Results By Order Of The IN' Clause

Jul 28, 2006

Consider this SQL:SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1','value3')Simple enough, but is there anyway to specify that the result should beordered exactly like the "IN" clause states? So when this recordsetcomes back, I want it like this:my_field------------value2value1value3Possible?Deane

View 5 Replies View Related

How To Limit Amount Of Results In A Search?

Jun 23, 2005

I'm busy writing a local site search engine that searches through a sql
server database and I want to know how or what is the correct sql
syntax to use in order to limit the amount of results a page loads at a
time? The idea is obviously similar to something like google where you
only see a certain amount of results first and then click at the botom
for the next eg. 10 results.

The second question is how do I, after the first page with the first
set of results that were shown, "clear" the second page of
the previous html in order to show the next set of results?
To give you an idea what my code looks like at the moment. Please don't
kill me if the code is done a bit a lame, because I'm still learning.

<%@ Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<%@ import Namespace="System.IO" %>
<script runat="server">

    void Page_Load(Object sender , EventArgs e)
    {
    //strings used to get values from basicSearch.aspx
       string strProvince;
       string strGender;
       string strHeight;
       string strBodyType;
       string strLooks;
       string strHairColor;
       string strEyeColor;
       string strEthnicity;
       string strHomeLanguage;
       string strRelStatus;
       string strRelInterest;
       string strHaveChildren;
       string strWantChildren;
    //strings used for storing results from search
       string resUserName;
       string resFirstName;
       string resLastName;
       string resUserPhoto;
       string resProvince;
       string resGender;
       string resAge;
       string resHeight;
       string resBodyType;
       string resLooks;
       string resHairColor;
       string resEyeColor;
       string resEthnicity;
       string resHomeLanguage;
       string resRelStatus;
       string resRelInterest;
       string resHaveChildren;
       string resWantChildren;
       string resProfileHeading;
       string resTextDescription;
       string resTextDescription2 = "";// used for the actual display of the value

       string strQuery; // to store concattenated search strings
       string strInput; // to store textfile input while being read
       StreamReader objStreamReader;

       strProvince     = Session["sessionProvince"].ToString();
       strGender       = Session["sessionGender"].ToString();
       strHeight       = Session["sessionHeight"].ToString();
       strBodyType     = Session["sessionBodyType"].ToString();
       strLooks        = Session["sessionLooks"].ToString();
       strHairColor    = Session["sessionHairColor"].ToString();
       strEyeColor     = Session["sessionEyeColor"].ToString();
       strEthnicity    = Session["sessionEthnicity"].ToString();
       strHomeLanguage = Session["sessionHomeLanguage"].ToString();
       strRelStatus    = Session["sessionRelStatus"].ToString();
       strRelInterest = Session["sessionRelInterest"].ToString();
       strHaveChildren = Session["sessionHaveChildren"].ToString();
       strWantChildren = Session["sessionWantChildren"].ToString();

       strQuery = strProvince + " " +
strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +

strHairColor + " " + strEyeColor + " " + strEthnicity + " " +
strHomeLanguage + " " +

strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "
+ strWantChildren;

       SqlConnection conPubs;
       string strSearch;
       SqlCommand cmdSearch;
       SqlDataReader dtrSearch;

       conPubs = new SqlConnection(
@"Server=THALIONTHALION;Integrated Security=SSPI;Database=DateGame" );
       //retrieve the results from the db
       strSearch = "SELECT * FROM
client," + "FREETEXTTABLE( client, * , @searchphrase ) searchTable "
+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
       cmdSearch = new SqlCommand( strSearch, conPubs );
       cmdSearch.Parameters.Add( "@searchphrase", strQuery );
       conPubs.Open();
       dtrSearch = cmdSearch.ExecuteReader();

       //start display of results
       lblResults.Text = "<table
width='100%' style='border-style:solid; border-width:thin;
border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
       while ( dtrSearch.Read())
       {
            //values read from the returned result set
            resUserName = dtrSearch[ "userName" ].ToString();
            resFirstName = dtrSearch[ "firstName" ].ToString();
            resLastName = dtrSearch[ "lastName" ].ToString();
            resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
            resProvince = dtrSearch[ "province" ].ToString();

resGender    = dtrSearch[ "gender" ].ToString();

resAge       = dtrSearch[ "age"
].ToString();

resHeight    = dtrSearch[ "height" ].ToString();
            resBodyType = dtrSearch[ "bodyType" ].ToString();

resLooks     = dtrSearch[ "looks" ].ToString();
            resHairColor = dtrSearch[ "hairColour" ].ToString();
            resEyeColor = dtrSearch[ "eyeColour" ].ToString();
            resEthnicity = dtrSearch[ "ethnicity" ].ToString();
            resHomeLanguage = dtrSearch[ "homeLang" ].ToString();

resRelStatus    = dtrSearch[ "relationshipStatus"
].ToString();

resRelInterest = dtrSearch[ "relationPreference" ].ToString();
            resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
            resWantChildren = dtrSearch[ "wantChildren" ].ToString();

resProfileHeading = dtrSearch[ "profileHeading" ].ToString();

resTextDescription = dtrSearch[ "textDescription" ].ToString();

            // read the text file's info into a variable for display
            if (
File.Exists( MapPath( "text files" +"\" +resTextDescription ) ) )
            {


objStreamReader = File.OpenText( MapPath( "text files" +"\"
+resTextDescription ) );

strInput = objStreamReader.ReadLine();

                while ( strInput != null)
                {


resTextDescription2 += strInput;

strInput = objStreamReader.ReadLine();
                }

objStreamReader.Close();
            }
            else
            {

resTextDescription2 = "myFile.txt does not exist!";
            }


//determine whether male or female in order to display correct sign
            if ( resGender == "Male")
                resGender = "Male_sign_1.jpg";
            else
                resGender = "Female_sign_1.jpg";


//determine whether 'want' and 'have' children and convert to correct
words for display
            if ( resHaveChildren == "have kids" )
                resHaveChildren = "Yes";
            else
                resHaveChildren = "No";

            if ( resWantChildren == "want kids" )
                resWantChildren = "Yes";
            else
                resWantChildren = "No";

            // The writing of html to display the values calculated


lblResults.Text += "<tr><td width='16%'
bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +
resUserName +

"</div><hr noshade class='hr_line'></td>";

lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'
class='text_bold'><div align='center'>" + resProfileHeading +

"</div><hr noshade class='hr_line'></td><td
colspan='2'><div align='center'
class='page_headings'>%</div></td></tr>";

lblResults.Text += "<tr><td rowspan='15' valign='top'
bgcolor='#C7C9BE'><p align='center'><img src='images/" +
resGender + "' width='20' height='22'></p>" +

"<img src='photos/" + resUserPhoto + "' width='80'
height='88'><div align='center'></div></td>";

lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><p
class='text'><br></p><p class='text'>" +
resTextDescription2 + "</p><p
class='text_bold'> </p></td>";

lblResults.Text += "<td width='7%' rowspan='15'
valign='top'><img src='images/hotlist_1.jpg' alt='Add To
Favorites' width='34' height='32'></td>" +

"<td width='14%' rowspan='15' valign='top'><img
src='images/email_1.jpg' alt='Email this profile' width='42'
height='36'></td></tr>";

lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'
class='text_bold'>Location :</td><td width='37%'
bgcolor='#E1E2DC' class='text'>" + resProvince +
"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Age :</td><td bgcolor='#E1E2DC'
class='text'>" + resAge + "</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Height : </td><td bgcolor='#E1E2DC'
class='text'>" + resHeight + "</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'
class='text'>" + resBodyType + "</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'
class='text'>"+ resLooks+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resHairColor+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'
class='text'>"+resEyeColor+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'
class='text'>"+resEthnicity+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Home Language : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHomeLanguage+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Relationship Status : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelStatus+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Realtionship Interest : </td><td
bgcolor='#E1E2DC'
class='text'>"+resRelInterest+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Have Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resHaveChildren+"</td></tr>";

lblResults.Text += "<tr><td bgcolor='#E1E2DC'
class='text_bold'>Want Children : </td><td
bgcolor='#E1E2DC'
class='text'>"+resWantChildren+"</td></tr>";

lblResults.Text += "<tr><td colspan='2'
bgcolor='#E1E2DC'> </td></tr>";

lblResults.Text += "<tr><td
bgcolor='#497792'> </td><td colspan='2'
bgcolor='#678BA1'> </td><td colspan='2'
bgcolor='#678BA1'> </td></tr>";

lblResults.Text += "<tr><td> </td><td
colspan='2'> </td><td
colspan='2'> </td></tr>";
            resTextDescription2 = "";
       }
            lblResults.Text += "</table>";
            conPubs.Close();

    }

    void Button_Login(Object sender , ImageClickEventArgs e)
    {

        SqlConnection conClient;
        string strSelect;
        string strclientName;
        SqlCommand cmdSelect;

        //create a connection
        conClient = new
SqlConnection( @"Server=THALIONTHALION;Integrated
Security=SSPI;database=DateGame" );

        //select statement
        strSelect = "Select userName
From [client] Where userName=@username and userPassword=@userpassword";
        cmdSelect = new SqlCommand( strSelect, conClient );

        cmdSelect.Parameters.Add( "@username", txtNickName.Text );
        cmdSelect.Parameters.Add( "@userpassword", txtPword.Text );

        //open a connection to db
        conClient.Open();

        //check to see if it already exists
        strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());

        if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
        {
            //Store user name as session variable
            Session["sessionUserName"] = txtNickName.Text.ToString();
            txtNickName.Text = "";
            txtPword.Text = "";
        }
        else

            lbl_invalid_login.Text = "Invalid login!";

        conClient.Close();
    }

</script>

View 1 Replies View Related

Ranking Results On XML Node Search?

Nov 14, 2006

hi.

i know this is a bit of a shot in the dark but, does anybody know of a reasonably understandable way to rank XML node path results based on keyword search terms? seems like there are tons of uber-complex docs on the internet about this. i dont mind if it is a bit of a work-around, i am just looking for something that is reasonable non-complex that i can wrap my mind around. links etc apprecaited.

thanks.

View 1 Replies View Related

Strip Out The From Alphabetized Search Results?

Jan 16, 2006

I am not aware of this if it exists in MS SQL server. But I need to return results in alphabetic order ignoring "The" if it's the first word of a title...

so for example title "The Cliffhanger" would be returned along with other titles that start with letter C, but "The" also must be returned as part of the title, but just ignored while alphabetizing.

I'm sure that I'm not the first one to ever need this and don't want to re-invent the wheel, so if you have any ideas as to the best way to accomplish this, help me out.

Thanks in advance.

View 8 Replies View Related

Search And Results Using Multiple Columns

Feb 10, 2004

I have a DBTable named Vendors which includes the following columns: CompanyName (Name Here)
SBI (Yes, No)
MBE (Yes, No)
SBI (Yes, No)
WBE (Yes, No)

I'm trying to do a search on Vendors where either SBI or MBE or SBI or WBE is equal to 'yes' and then order by company name.

I'm lost on both the search and the results code.

I'd like to have a drop down for the search like this:

<form name="Search" action="vendor_results.asp" method="post">
<Input type=hidden name="validate" value=1>

Show all Vendors who are: <Select name="?????">
<OPTION value="YES">SBI
<OPTION value="YES">SBE
<OPTION value="Yes">MBE
<OPTION value="Yes">WBE
</select>
<Input type="submit" Value="Go">
</form>

Any ideas on how to make this work would be greatly appreciated.

View 3 Replies View Related

Selecting A Subset Of The Search Results

Nov 2, 2007

I want to break up a set of search results into small chunks. For instance, think about how Google displays a block of ten results out of the entire set. Selecting the top 10 is dead easy with "TOP 10"; how do I select the next 10?

Obviously one option would be to select the top 20, and programmatically discard the first 10, but surely there is a better way? I am doing this for a ASP.NET 2.0 application, and if I can retrieve just what I want, I can DataBind to a Repeater, and let ASP.NET do all the hard work of displaying the data.

View 4 Replies View Related

MS Access- TOP / Offset? I Need To Order Results As Pages

Feb 18, 2005

Hi,

Sorry I have to post this here, but its sort of related to MS SQL anyway

I'm running a PHP system with MS Access. I need to order results in pages. (For those who are familiar with MySqL and Postgresql - I need the equivalent to LIMIT/OFFSET in MS Access).

I know I can "SELECT TOP 50" in my sql - so that solves the number of results per page.
But what about page 2,3,4..etc.... how do I select results from an offset?

There doesnt seem to be a solution for it

Thanks!

View 2 Replies View Related

SQL Server 2012 :: Get Results In A Specific Order?

Sep 11, 2014

I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:

create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)

[Code].....

I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:

HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas

View 7 Replies View Related

Brain Teaser - Stagger The Order Of The Results

Sep 5, 2005

This one has been stumping me for several days. I can run a query thatreturns several different items from several different manufacturers,each with a ranking score. Each manufacturer can have any number ofitems:Item_Name Manufacturer rankItem 1 Manu_A 82Item 2 Manu_A 65Item 3 Manu_A 41Item 4 Manu_B 32Item 5 Manu_C 21Item 6 Manu_B 19However, I would like the records to be ordered so that the highestranking item is shown first, then the next highest item from adifferent manufacturer is shown second, then the next highest item froma third manufacturer is show, etc.:Item 1 Manu_A 82Item 4 Manu_B 32Item 5 Manu_C 21Item 2 Manu_A 65Item 6 Manu_B 19Item 3 Manu_A 41Does anyone have any thoughts on how to order the results in thisfashion?thanks,Matt Weiner

View 2 Replies View Related

3 Results From One Field - Show Levels In Right Order

Nov 19, 2007

Hi there

We have a web application (database) that uses one field called Application and another called TicketType.

When a user fills out a ticket they can choose up to 3 levels of this field.
Eg Application, Application2, Application3

Eg TicketType, TicketType2, TicketType3

The extra two levels not being compulsory.

I am using sql server 2005 // Reporting Services

My query is as below:
SELECT Ticket.TicketNumber, Ticket.CreatedDate, Application_2.ApplicationName AS Application, Application_1.ApplicationName AS [App 2],
Application.ApplicationName AS [App 3], TicketType_2.TicketTypeName AS Tickettype, TicketType_1.TicketTypeName AS [Type 2],
TicketType.TicketTypeName AS [Type 3], Ticket.Description, Company.CompanyName
FROM Ticket INNER JOIN
TicketType AS TicketType ON Ticket.TicketTypeID = TicketType.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_1 ON TicketType.ParentTicketTypeID = TicketType_1.TicketTypeID LEFT OUTER JOIN
TicketType AS TicketType_2 ON TicketType_1.ParentTicketTypeID = TicketType_2.TicketTypeID INNER JOIN
Application AS Application ON Ticket.ApplicationID = Application.ApplicationID INNER JOIN
Company ON Application.CompanyID = Company.CompanyID FULL OUTER JOIN
Application AS Application_1 ON Application.ParentApplicationID = Application_1.ApplicationID FULL OUTER JOIN
Application AS Application_2 ON Application_1.ParentApplicationID = Application_2.ApplicationID
WHERE (Ticket.CreatedDate >= @StartDate)
ORDER BY Ticket.TicketNumber

End result looks like this:

Application

App 2

App 3

TicketType

Type 2

Type 3

Software

Internal Apps

proACT

SW Other

Office Issues

General

Application

Click Track server

Alert (App)

Service

Network

Other

Network Fault

Software

Internal Apps

Other

User Account

New

Hardware

Network

HW Fault

Application

Click Track server

Alert (App)

Disk space

Office Issues

General

proACT

Configuration

Deployment

Software

Server Software

SharePoint

SW Fault

App Failure (Function)

Software

Server Software

SharePoint

SW Fault

App Failure (Function)

Ultimately I would like the Application (TicketType) fields to have the Master Information in it and the other two fields populated in order as well.

Can someone help please.

Please ask if I haven't explained myself.

thanks
Dianne

View 9 Replies View Related

Best Way To Order Results Sequentially Starting From Somewhere In The Middle

Apr 19, 2007

I'm working with SQL Server 2005, and I'm trying to sort the results based on a user selected letter. Say the user selects 'D' to filter his results. I'd like to return the results starting from D followed by E, F, G...Z, A, B, C. What I'm getting is the results for the D entries at the top of the result set, followed by A, B, C, E...Z.

A solution comes to mind that would be very long and db intensive, by querying on 'like 'D', followed by like 'E', followed by like 'F', etc, but I'm sure that there is a much more efficient way to do this. Below is the code that I'm using now.

' where @SortString = 'd' and @Test is a temp Table

BEGIN

Insert into @Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName like @SortString +'%'

Order by CompanyName

Insert into @Test

Select CompanyName,ContactId, CompanyId

from vContacts where CompanyName not like @SortString +'%'

Order by CompanyName

END

Thanks in advance for your help

View 3 Replies View Related

Extract Distinct Information And Order The Results

Sep 24, 2007

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.

I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description
5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.

SELECT * FROM SAMPLE
WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query
5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards
Deceptive

View 9 Replies View Related

Order By Cluase Cause Wrong Results To Be Returned.

Sep 10, 2007

I have the follow table.

/****** Object: Table [dbo].[deletethisTempOut] Script Date: 09/10/2007 09:20:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[deletethisTempOut](
[ThemeName] [varchar](60) NULL,
[intLocationCount] [int] NULL,
[dblRepValueA] [float] NULL,
[dblRepValueB] [float] NULL,
[dblRepValueC] [float] NULL,
[dblRepValueD] [float] NULL,
[dblTotalRepValue] [float] NULL,
[dblLimit1] [float] NULL,
[dblLimit2] [float] NULL,
[dblLimit3] [float] NULL,
[dblLimit4] [float] NULL,
[dblTotalLimit] [float] NULL,
[fltEmployeecount] [float] NULL,
[intAreaLevel1] [tinyint] NOT NULL,
[strFullName] [varchar](13) NOT NULL,
[strAreaLevel2] [varchar](20) NOT NULL,
[strAreaLevel3] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

If I use the following SQL:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY strAreaLevel2, strAreaLevel3
GET Following correct results:

Adair
284
899989594
0
574857716
190479902
1665327212
0
0
0
0
1665327212
0
1
United States
1
1
IF I use the following SQL I get the wrong results:

SELECT ThemeName, intLocationCount, dblRepValueA, dblRepValueB, dblRepValueC, dblRepValueD, dblTotalRepValue, dblLimit1, dblLimit2, dblLimit3,
dblLimit4, dblTotalLimit, fltEmployeecount, intAreaLevel1, strFullName, strAreaLevel2, strAreaLevel3
FROM deletethisTempOut
ORDER BY ThemeName

WRONG results:

Adair
74
81733110
0
49616018
24671651
156020779
50510500
0
0
0
203870779
0
1
United States
50
1

Adair
437
1468698657
0
495479839
353202768
2317381264
12984266
0
0
0
2315676030
0
1
United States
25
1

Adair
1813
20309722045
0
6597005374
4253819645
31160547064
43636703
0
0
0
31135010742
0
1
United States
11
1

Adair
606
439581417
0
331746662
132240332
903568411
0
0
0
0
903568411
0
1
United States
45
1

Adair
236
350256381
0
524269553
504973831
1379499765
4080368
0
0
0
1380473415
0
1
United States
23
1etc.....

View 6 Replies View Related

Full-text Search With Order By Or Group By Is Unusable

Feb 13, 2008

Using SQL Server 2005 (9.0.3042), I can not get a full-text search query to work with order by or group by. For example, if I run the following query...

SELECT s.networkID, i.interID

FROM Interactions i

INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID

INNER JOIN Sessions s ON i.sessionID = s.sessionID

WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000

It returns two rows immediately.

If I add an "order by" or "group by" like...

SELECT s.networkID, i.interID

FROM Interactions i

INNER JOIN CONTAINSTABLE(Interactions, text, 'ipo') t ON t.[key] = i.interID

INNER JOIN Sessions s ON i.sessionID = s.sessionID

WHERE i.startTime BETWEEN 1051772400000 AND 1054537199000

ORDER BY s.networkID, i.interID

The CPU shoots to 100% and I have to kill the query. I have tried many variations of the above and I can not get the query to work with order by or group by. Is this a bug in SQL Server? Is there a fix or workaround?

View 1 Replies View Related

SQL7: Order Of Values In IN Clause Affects Results

Dec 14, 1999

Hi!
Has anyone experienced this problem?
Certain queries that work fine in SQL 6.5 and Oracle return inconsistent / inaccurate results in SQL 7 (with SP1). These queries include an IN clause with a range of values.
For example, the following query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,2,3,11,19)
go

returns a different result than this query:
SELECT columnA, columnB, columnC, columnD
FROM table
WHERE columnD = 'I'
AND columnA IN (1,3,11,2,19)
go

The only way we have stumbled upon to get accurate results consistently is to order the range values from largest to smallest:
AND columnA IN (19,11,3,2,1)

Have not seen this documented anywhere. We are in the process of re-ordering these ranges in our code, but I welcome any ideas or comments...
Thanks!

View 2 Replies View Related