SQL Server – How to Order by Custom Filter Excluding Certain Data

sql server

I have a similar issue to the question in:

ORDER BY using custom priorities for text columns

…except that I want to only get the data out if I have certain values. To clarify, we have a table that stores both email addresses and phone numbers in the same column.

I cannot change this structure without breaking a critical system that I do not have source code access to. I need to be able to retrieve only email addresses, and have the query show NULL when there is no email address.

The table structure is:

ID (Int)
Description Code (Int) (can use same values for both emails and phone numbers, nothing distinguishing here)
Number (varchar)

Right now, similar to the question above, I have my SQL as:

order by case 
    when con2.NUMBER like '%@ourdomain.com' then 1 
    when con2.NUMBER like '%@%' then 2
    else 3 
    end

I would like to not show phone numbers in this output. Is there an easy adjustment to my code to facilitate this?

Example

Records for Sally

ID    Type     Number
1     1011     (215)555-1213
2     1011     (215)555-1312
3     1012     sally@ourdomain.com
4     1013     sallyf@gmail.com

Records for Frank

ID    Type     Number
5     1013     frankiebaby@yahoo.com
6     1013     franks@ibm.com

Records for Julius

ID    Type     Number
7     1010     555-123-9876
8     1011     425-369-1470

Output would return sally@ourdomain.com for Sally and frankiebaby@yahoo.com for Frank, but NULL for Julius.

I need to prioritize one domain over any others, so addresses in our own domain would show up if it's available, if not, then google, hotmail, or whatever else comes secondary.

Best Answer

This will get your result set, though I did add the Name field for clarity. The common table expression (cte) with ROW_NUMBER() will get the email priority. Then I left joined that back to the base table to return the names and a NULL for Julius.

CREATE TABLE #emailOrPhone (ID INT IDENTITY(1,1), Type INT, Number VARCHAR(50), Name VARCHAR(20))

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1213','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'(215)555-1312','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1012,'sally@ourdomain.com','Sally')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'sallyf@gmail.com','Sally')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'frankiebaby@yahoo.com','Frank')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1013,'franks@ibm.com','Frank')

INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1010,'555-123-9876','Julius')
INSERT INTO #emailOrPhone (Type, Number, Name) VALUES (1011,'425-369-1470','Julius')

;WITH cte AS
(
SELECT Name, Number
, CASE WHEN Number like '%@ourdomain.com' THEN 1 ELSE 2 END AS 'emailPriority'
, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Name) as rn
FROM #emailOrPhone
WHERE Number like '%@%'
)

SELECT DISTINCT Names.Name, Emails.Number
FROM #emailOrPhone as Names
LEFT JOIN cte as Emails on Names.Name = Emails.Name and Emails.rn = 1
ORDER BY Names.Name, Emails.Number

DROP TABLE #emailOrPhone

Related Solutions

Sql-server – Should I extract / normalize the mailing address despite requirements limiting number / type of addresses

Always bet on requirements changing, or being reinterpreted, at some point in the future.

Does the requirements documentation explicitly state that there are exactly two or just that there should be up to one primary and up to one secondary? If the latter then what could be meant is there could be many addresses of which up to one is the primary and up to one is the secondary and you should allow for more addresses.

Even if not, I would still err on the side of addresses being separate entities if you need to support more than one for any other given entity. The extra work now should be minimal, if you need to emulate the non-normal-form for the application logic you can by various means, and when the requirements change later your job will be a lot easier (and you can either impress the client/devs with your foresight or keep shtum and charge them as if the change request does require significant structural alterations in the DB).

Sql-server – T-SQL – How to filter data from table

If you want to return the minimum id for each name, then there are a few ways that you can get the result.

One way would be to use an aggregate function in a subquery. The subquery will return the min(id) for each name, you then use this subquery an join it back to your table on those two columns:

select t1.name,
  t1.value,
  t1.id
from yourtable t1
inner join
(
  select min(id) id, name
  from yourtable
  group by name
) t2
  on t1.id = t2.id
  and t1.name = t2.name

See SQL Fiddle with Demo

Another way you can get the result would be using the row_number() windowing function. This will allow you to create a unique identifier for each row based on the name and order the result by the id, you get the final result by returning only those rows with a sequence value of 1:

select name, value, id
from
(
  select name, value, id,
    row_number() over(partition by name
                      order by id) seq
  from yourtable
) d
where seq = 1;

See SQL Fiddle with Demo. Both will return a result:

| NAME | VALUE | ID |
|------|-------|----|
|    A |    15 |  1 |
|    B |    12 |  1 |
|    C |    12 |  1 |

Example

Best Answer

Related Solutions

Sql-server – Should I extract / normalize the mailing address despite requirements limiting number / type of addresses

Sql-server – T-SQL – How to filter data from table

Related Question