Eli <[EMAIL PROTECTED]> wrote on 02/08/2005 02:26:41 PM:
> Hello,
>
> Say I get these rows in a regular query:
>
> col1 col2 col3
> -----------------------
> NULL B1 NULL
> NULL NULL NULL
> A3 B3 NULL
> A4 NULL C4
> A5 B5 C5
> NULL B6 C6
>
> (It's important to keep the rows in that order).
> I want to get 1 row of the first non-null values from every column.. the
> row: A3 B1 C4.
>
> Like the COALESCE function in MySQL, but on rows.
>
> Please help...
>
> -thanks, Eli
>
IF you want all 3 columns to be non-null, make that a condition of your
query.
SELECT col1, col2, col3
FROM sometable
WHERE col1 is not null
AND col2 is not null
AND col3 is not null
LIMIT 1;
However, you cannot guarantee a repeatable order to the results of any
query unless you force the engine to sort the results by providing an
ORDER BY clause to your query. Without an ORDER BY, the query engine is
free to respond with records in any order it pleases.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
