TSQL to choose a record that meets the criteria or first one

Question

I have a table for company phone numbers and one of the columns is IsPrimary which is a boolean type. The table looks like this:

CompanyId  |  AreaCode  |  PhoneNumber | IsPrimary
123            212         555-1212      0
234            307         555-1234      1
234            307         555-4321      0

As you can see in the first record, even though the phone number is the only one for CompanyId: 123, it's not marked as the primary.

In such cases, I want my SELECT statement to return the first available number for that company.

My current SELECT statement looks like this which does NOT return a number unless it's set as the primary number.

SELECT *
FROM CompanyPhoneNumbers AS t
WHERE t.IsPrimary = 1

How can I modify this SELECT statement so that it includes the phone number for CompanyId: 123?

So you want to partition by companyid and order by isprimary desc then select the first one for each of these? — ZLK, Mar 20 '19 at 00:32
How do you define "first"? Having a first value implies an order of some kind. — DavidG, Mar 20 '19 at 00:32
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — DavidG, Mar 20 '19 at 00:37
Unless you have a column to use for `order by`, [There is no "first" row.](https://stackoverflow.com/questions/48168234/how-to-get-the-last-row-from-a-table-in-sql-server/48168275#48168275) — Zohar Peled, Mar 20 '19 at 13:52

score 0 · Accepted Answer · answered Mar 20 '19 at 01:07

The query might be different depending on what you are actually up to.

If you already have the CompanyId and only need the phone number for it, that's easy:

select top (1) pn.*
from dbo.CompanyPhoneNumbers pn
where pn.CompanyId = @CompanyId -- A parameter provided externally, by calling code for instance
order by pn.IsPrimary desc;

However, if you need all companies' data, including one of their phones (for example, you might be going to create a view for this), then you need a correlated subquery:

select c.*, oa.*
from dbo.Companies c
  outer apply (
    select top (1) pn.*
    from dbo.CompanyPhoneNumbers pn
    where pn.CompanyId = c.Id
    order by pn.IsPrimary desc
    ) oa;

I have deliberately used outer instead of cross apply, otherwise it will filter out companies with no phone numbers listed.

score 0 · Answer 2 · answered Mar 20 '19 at 01:56

You can achieve this using an apply statement. This looks at the exact same table and returns the record with the highest IsPrimary so, this would return the records with a 1 in that column. If there are more than one marked as primary or not as primary, then it returns the phone number, with area code, in ascending order.

select      b.*
from        CompanyPhoneNumbers     a
cross apply (
                select      top 1
                            *
                from        CompanyPhoneNumbers b
                where       b.CompanyId = a.CompanyId
                order by    b.IsPrimary desc
                            ,b.AreaCode
                            ,b.PhoneNumber
            )                       b

TSQL to choose a record that meets the criteria or first one

2 Answers2