|
|
Posted by Hugo Kornelis on 05/27/06 01:02
On 26 May 2006 12:07:40 -0700, skosmicki@sfmc-gi.org wrote:
>I need to create an function similar to the "MATCH" function in Excel
>that evaluates a number within a set of numbers and returns whether
>there is a match. I have put the example of what I see in excel in the
>check column. The "0" answer in the result column is in the fourth
>account in the list.
(snip)
Hi skosmicki,
Fourth by what definition? I can see that it's fourth in the order yoou
wrote the rows, but I don't see any appparent logic in the ordering of
rows with the same account. Remember that SQL Server doesn't keep track
of the order in which rows are inserted - if that's relevant to you,
you'll have to add a column for it.
> Somehow I need to loop through the accounts
>comparing the result to the total and indicate a match in the check
>column. It wouldn't even need to tell me the row number; it could be a
>0 or 1.
Maybe something like this? (Untested - see www.aspfaq.com/5006 if you
prefer a tested reply)
SELECT a.account, a.total, a.result,
CASE WHEN b.account IS NOT NULL THEN 1 ELSE 0 END AS check
FROM YourTable AS a
LEFT OUTER JOIN YourTable AS b
ON a.account = b.account
AND a.result = b.total
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|