I dont know weather this is the right place to post this issue. If not please suggest me , I did not find anything right then this one.
Ok now the problem.
I've got a table
TABLE_A
---------
name -- vharchar(50)
age -- integer
DOB -- date
I need to select records from this table depending on a selection criterion. thats easy , but i want to know that how many columns matched in the searching because i need to store the rating for how many columns matched for a particular search.
Ex. if name , age and DOB matched rating is A3
if name , age matches rating is A2 and so on.
This table is a huge one having 20 Million records, so I want a solution that is optimized too.
SQL problem !
techguy52
How you are searching the record Can you post your search query here.. I got lot of assumption here.. Better I ask & get from you..
Bear23
Here I am really confused to use Count(*) Here..
Count(*) will return the number of search result.. But What i understood was get the each rows Search Result Rank.
The following sample query can help you to find this rank.. But what i really confused is How you are going to matach the values, is it exact Equal Operation or Like or etc...
Declare @SearchTable Table
(
Col1 varchar(100),
Col2 varchar(100),
Col3 varchar(100)
);
Insert Into @SearchTable Values('M','M','M');
Insert Into @SearchTable Values('M','M','N');
Insert Into @SearchTable Values('M','N','N');
Insert Into @SearchTable Values('N','N','N');
Insert Into @SearchTable Values('N','N','M');
Insert Into @SearchTable Values('N','M','M');
Declare @SearchableString as Varchar(100);
Select @SearchableString = 'M'
Select
Col1
,Col2
,Col3
,Case When CharIndex(@SearchableString,Col1) <> 0 Then 1 Else 0 End
+ Case When CharIndex(@SearchableString,Col2) <> 0 Then 1 Else 0 End
+ Case When CharIndex(@SearchableString,Col3) <> 0 Then 1 Else 0 End
as Rank
From @SearchTable
Result:
Col1 Col2 Col3 Rank
---- ---- ---- -----------
M M M 3
M M N 2
M N N 1
N N N 0
N N M 1
N M M 2
darngar
Andrew Butenko
hi,
put a count(*) in your query and group by then name,age,rating.
Like
Select Name, age,rating , count(*) as 'Count' from Table
group by name,age,rating
Sam4u2e