I've a table called Index
id fid value
1 1 0
1 2 20
1 3 15000
1 4 1
1 5 2
2 1 1
2 2 0
2 3 20000
2 4 2
2 5 0
3 1 2
3 2 0
3 3 20000
3 4 1
3 5 0
4 1 1
4 2 24
4 3 15000
4 4 3
4 5 3
5 1 0
5 2 0
5 2 0
5 3 0
5 4 0
5 5 0
6 1 0
6 2 20
6 3 15000
6 4 1
6 5 5
7 1 0
7 2 20000
7 3 0
7 4 2
7 5 0

and I'm using the following query to retrieve data from table
select distinct lid from index where
(fid = 1 && (value = 1 || value = 0)) ||
(fid = 2 && (value = 24 || value = 0)) ||
(fid = 3 && (value = 15000 || value = 0)) ||
(fid = 4 && (value = 3 || value = 0)) ||
(fid = 5 && (value = 3 || value = 0))

Its returns the results but i want the results in the order of most where condition matches. eg:
n the attached index table take lid 4, it has the values like
4 1 1
4 2 24
4 3 15000
4 4 3
4 5 3
this is what the query looking for, so the lid 4 should come on first and then rest results.

Expected output:
4 => matched all conditions
2 => matched first where condition
1 => matched third where condition
6 => matched third where condition
5 => matched with value = 0 condition
7 => matched with value = 0 condition

Thanks in advance

so which question do you want answers to

the one here in ANSI standard SQL so theoreticaly portable to all variants of SQl

the one in SQL server, the Microsoft propriatory dialect of SQL

or Oracles propriatory dialect

or MySQL's propriatory dialect?

Any one,

This is the question for different forum people / DB gurus, if any one of them answered me, that will be great.

I believe, a Oracle Guru don't visit MySQL or other part and vice verse, that's why I've posted in different places

Thanks,

Please don't cross post. Since you haven't identified your RDBMS I will retain this version in ANSI SQL.

As stated, PL\SQL != T-SQL != mySQL.

Something like this?

select id,
count(distinct case when fid = 1 and (value = 1 or value = 0) then 1
when fid = 2 and (value = 24 or value = 0) then 2
when fid = 3 and (value = 15000 or value = 0) then 3
when fid = 4 and (value = 3 or value = 0) then 4
when fid = 5 and (value = 3 or value = 0) then 5 end)
as cnt
from index where
(fid = 1 and (value = 1 or value = 0)) or
(fid = 2 and (value = 24 or value = 0)) or
(fid = 3 and (value = 15000 or value = 0)) or
(fid = 4 and (value = 3 or value = 0)) or
(fid = 5 and (value = 3 or value = 0))
group by id
order by cnt desc


SQL-99 compliant, using the non-core feature F561, "Full value expressions".

JarlH - good query, but I think you want to remove the COUNT from your CASE expression and add in a third column of COUNT(*), right?

BTW - my strong preference for this sort of logic is to store these values in a table and reference via a join, for many of the reasons here: Data belongs in your tables -- not in your code
I'm not sure what the structure of this table would be since we don't know the business rules, just the outputs.

Perhaps thats what ilayasoft is looking for.

I don't know if the result should be ordered by distinct matches or just by matches.

EDIT - Apologies, I misread the question.

thank you so much for your suggestion,
I've already written a query some thing similar like this the query is

SELECT lid,
SUM(
CASE fid
WHEN 1 THEN
CASE li.value WHEN 1 THEN 1 WHEN 0 THEN 1 ELSE 0 END
WHEN 2 THEN
CASE li.value WHEN 24 THEN 1 WHEN 0 THEN 1 ELSE 0 END
WHEN 3 THEN
CASE li.value WHEN 15000 THEN 1 WHEN 0 THEN 1 ELSE 0 END
WHEN 4 THEN
CASE li.value WHEN 3 THEN 1 WHEN 0 THEN 1 ELSE 0 END
WHEN 5 THEN
CASE li.value WHEN THEN 1 WHEN 0 THEN 1 ELSE 0 END
END ) AS rank
FROM index
GROUP BY lid
ORDER BY rank DESC