Eli <[EMAIL PROTECTED]> wrote on 02/08/2005 03:27:42 PM: > > 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 > > > > Sorry, but that is not the case I'm looking for.. :-( > The row I need is: A3 B1 C4 (the first non-null values from several rows). > Your query will return: A5 B5 C5 (row 5 only). > > Mabye there's a way to use COALESCE function in MySQL, but I > couldn't figure how... > > -thanks, Eli > > -- With the warning about ordering in mind you could try this: SELECT @col1 = col1 FROM sometable WHERE col1 is not null LIMIT 1; SELECT @col2 = col2 FROM sometable WHERE col2 is not null LIMIT 1; SELECT @col1 as col1, @col2 as col2, col3 FROM sometable WHERE col3 is not null LIMIT 1; Shawn Green Database Administrator Unimin Corporation - Spruce Pine
