CODEWITHSUNDEEP
sybasesap-ase
user7180132
user7180132

Reputation: 421

Sybase - how do I return the first value that exists from a condition in SQL?

Say I'm trying to return some results where a column in a table matches a condition I set. But I only want to return the first result from a list of possible values in the condition. Is there a quick and easy way to do that? I'm thinking that I can use coalesce somehow, but not sure how I can structure it.

Something like:

select identifier,purpose from table
where identifier = 'letters'
and purpose = coalesce('A','B','C')
group by purpose

So in the table, if A purpose isn't there, then I only want the B purpose to show up. if it isn't there, then I want the C to show up, if none of them are there, then I would ideally like a null or no results to be returned. I'd rather not make several case statements where if A is null then look to B, then if B is null to look to C. Is there a quick way syntactically to do so?

Edit: I also want this to work if I have multiple identifiers I list, such as:

select identifier,purpose from table
where identifier in ('letters1', 'letters2')
and purpose = coalesce('A','B','C')
group by purpose

where I return two results if they exist - one purpose for each identifier, with the purpose in the order of importance for A first, then B, then C, or null if none exist.

Unforunately my reasoning for caolesce doesn't work above, as none of the variables are null so my query will just try to return all purposes of 'A' without the fallback that I intend my query to do. I want to try and avoid using temp tables if possible.

Upvotes: 0

Views: 1374

Answers (1)

markp-fuso
markp-fuso

Reputation: 35006

Sybase ASE does not have support for the row_number() function (else this would be fairly simple), so one option would be to use a #temp table to simulate (to some extent) row_number() functionality.

Some sample data:

create table mytab
(identifier     varchar(30)
,purpose        varchar(30)
)
go

insert mytab values ('letters1','A')
insert mytab values ('letters1','B')
insert mytab values ('letters1','C')

insert mytab values ('letters2','A')
insert mytab values ('letters2','B')
insert mytab values ('letters2','C')
go

The #temp table is created with an identity column plus a 2nd column to hold the items you wish to prioritize; priority is determined by the order in which the rows are inserted into the #temp table.

create table #priority
(id        smallint    identity
,purpose   varchar(30))
go

insert #priority (purpose)
select 'A'                  -- first  priority
union all
select 'B'                  -- second priority
union all
select 'C'                  -- last   priority
go

select * from #priority order by id
go

 id     purpose
 ------ -------
      1 A
      2 B
      3 C

We'll use a derived table to find the highest priority purpose (ie, minimal id value). We then join this minimal id back to #priority to generate the final result set:

select  dt.identifier,
        p.purpose

from    (-- join mytab with #priority, keeping only the minimal priority id of the rows that exist:

         select m.identifier,
                min(p.id)       as min_id

         from   mytab m

         join   #priority p
         on     p.purpose = m.purpose

         group by m.identifier) dt

-- join back to #priority to convert min(id) into the actual purpose:

join    #priority p
on      p.id = dt.min_id

order by 1
go

Some test runs with different set of mytab data:

/* contents of mytab:

insert mytab values ('letters1','A')
insert mytab values ('letters1','B')
insert mytab values ('letters1','C')

insert mytab values ('letters2','A')
insert mytab values ('letters2','B')
insert mytab values ('letters2','C')
*/

 identifier purpose
 ---------- -------
 letters1         A
 letters2         A

/* contents of mytab:

--insert mytab values ('letters1','A')
--insert mytab values ('letters1','B')
insert mytab values ('letters1','C')

--insert mytab values ('letters2','A')
insert mytab values ('letters2','B')
insert mytab values ('letters2','C')
*/

 identifier purpose
 ---------- -------
 letters1         C
 letters2         B

Returning NULL if a row does not exist is not going to be easy since generating a NULL requires existence of a row ... somewhere ... with which to associate the NULL.

One idea would be to expand on the #temp table idea by creating another #temp table (eg, #identifiers) with the list of desired identifier values you wish to search on. You could then make use of a left (outer) join from #identifiers to mytab to ensure you always generate a result record for each identifier.

Upvotes: 1

Related Questions