Is possible have different conditions for each row in a query?

Question

How I can select a set of rows where each row match a different condition?

Example:

Supposing I have a table with a column called name, I want the result ONLY IF the first row name matches 'A', the second row name matches 'B' and the third row name matches 'C'.

Edit:

I want to do this to work without a fixed size, but in a way I can define the sequence like R,X,V,P,T and it matches the sequence, each one in a row, but in the order.

Are you sure you mean row? as thats completely dependent on the order by if it exists? — Chris, Nov 23 '11 at 21:54
@Chris Yes, completely dependent on the order because the order is high important in what I want to do, and yes, the order exists. — Renato Dinhani, Nov 23 '11 at 21:57
Is there a field that defines the order? There is no "natural" order implied in SQL — alexm, Nov 23 '11 at 21:58
Maybe i don't understand you problem very well. You can do this with using union. something like this: select * from tab where name=A union select * from tab where name=B union select * from tab where name=A — JercSi, Nov 23 '11 at 21:59
What RDBMS? I"m thinking if you treat the query as a hierichal query then it MIGHT work. (I like the question though) — xQbert, Nov 23 '11 at 22:00
@alexm The real case a bit more complex than this, so call the order field as `position`. — Renato Dinhani, Nov 23 '11 at 22:02
@Renato: and what do you want to be returned? Only the first 3 rows which match your condition, or every row afterwards, or do you want every list of 3 rows matching that condition? — Doc Brown, Nov 23 '11 at 22:07
@DocBrown Every list of the 3 sequential rows that match the condition. — Renato Dinhani, Nov 23 '11 at 22:09
Well I'm out: I dont' know postgresql well enough: but just for consideration: could the values be rows in a separate table of N size? — xQbert, Nov 23 '11 at 22:16

score 2 · Answer 1 · answered Nov 23 '11 at 22:03

2

you can, but probably not in a way you would want:

if your table has a numeric id field, that is incremented with each row, you can self join that table 3 times (lets say as "a", "b" and "c") and use the join condition a.id + 1 = b.id and b.id + 1 = c.id and put you filter in a where clause like: a.name = 'A' AND b.name = 'B' AND c.name = 'C'

but don't expect performance ...

answered Nov 23 '11 at 22:03

DarkSquirrel42

9,807
3
19
30

Only drawback (beside potential problem with performance) is that You have to change your query if the number of conditions change. Am I missing something obvious or this way query will not return set of 3 rows when all of them match, but it will return one row with columns from all 3 rows? – Filip Popović Nov 23 '11 at 22:23
"but probably not in a way you would want" ... ;) ... you could create the query by some piece of code if it changes often, and yes, this will give you 1 row instead of 3 ... to solve the problem with the number of rows, you could select the row IDs, and fetch the corresponding single rows with a second query ... ugly, i know ... – DarkSquirrel42 Nov 23 '11 at 22:30

score 1 · Accepted Answer · answered Nov 23 '11 at 22:20

Assuming that You know how to provide a row number to your rows (ROW_NUMBER() in SQL Server, for instance), You can create a lookup (match) table and join on it. See below for explanation:

LookupTable:

RowNum    Value
1         A
2         B
3         C

Your SourceTable source table (assuming You already added RowNum to it-in case You didn't, just introduce subquery for it (or CTE for SQL Server 2005 or newer):

RowNum Name
-----------
1      A
2      B
3      C
4      D

Now You need to inner join LookupTable with your SourceTable on LookupTable.RowNum = SourceTable.RowNum AND LookupTable.Name = SourceTable.Name. Then do a left join of this result with LookupTable on RowNum only. If there is LookupTable.RowNum IS NULL in final result then You know that there is no complete match on at least one row.

Here is code for joins:

SELECT T.*, LT2.RowNum AS Matched 
FROM LookupTable LT2
LEFT JOIN 
(
    SELECT ST.*
    FROM SourceTable ST
    INNER JOIN LookupTable LT ON LT.RowNum = ST.RowNum AND LT.Name = ST.Name
) T
    ON LT2.RowNum = T.RowNum

Result set of above query will contain rows with Matched IS NULL if row is not matching condition from LookupTable table.

I'm with problem getting the row numbers. It's possible create a window from a number to itself? Example: start the `row_number()` count when found 595 and restarting when find again? — Renato Dinhani, Nov 23 '11 at 23:25
@RenatoDinhaniConceição, I guess not... in SQL Server You can UPDATE your table with clustered index with variable: http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx ... But You can restart numbering on groups. Take a look at PARTITION BY in ROW_NUMBER or RANK at http://msdn.microsoft.com/en-us/library/ms176102.aspx — Filip Popović, Nov 23 '11 at 23:43
I used part of your logic to do a function to solve my problem. Thanks. — Renato Dinhani, Nov 24 '11 at 17:14

score 0 · Answer 3 · answered Nov 23 '11 at 21:59

0

I suppose you could do a sub query for each row, but it wouldn't perform well or scale well at all and would be hard to maintain.

answered Nov 23 '11 at 21:59

Daryl

18,221
8
71
138

xQbert · Answer 4 · 2011-12-21T23:21:15.610

0

This may be close to what your after... but I need to know where you're getting your values for A, B, C etc...

Select [insert your fields here]
FROM
(Select T1.Name, T1.Age, RowNum as t1RowNum from T T1 order by name) T1O
Full Outer JOIN 
(Select T2.Name, T2.Age, RowNum as T2rowNum From T T2 order By name) T2O
ON T1O.T1RowNum+1 = T2O.T2RowNum

edited Dec 21 '11 at 23:21

answered Nov 23 '11 at 22:12

xQbert

33,725
2
39
60

**Never use *, always define the fields.** – zerkms Dec 21 '11 at 23:13

Is possible have different conditions for each row in a query?

4 Answers4

Linked