Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Question Unanswered: Need help on query!

    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
    Last edited by ilayasoft; 03-17-10 at 03:39.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    6
    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,

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Last edited by pootle flump; 03-17-10 at 07:34.

  5. #5
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    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".

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by pootle flump View Post
    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?
    Perhaps thats what ilayasoft is looking for.

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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    EDIT - Apologies, I misread the question.
    Last edited by pootle flump; 03-17-10 at 08:45.

  9. #9
    Join Date
    Mar 2010
    Posts
    6
    Quote Originally Posted by JarlH View Post
    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".
    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


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •