SQL pulling distinct values and creating an ID

Question

I've got this query:
select
    p.[PersonID],
    d.[DeviceID],
    'DeviceID' + convert(char, ROW_NUMBER() over(partition by p.PersonID order by d.DeviceID)) as 'key'
from People p
    inner join Devices d
    on p.PersonID = d.PersonID
    where d.DeviceID is not null

From these tables:
PersonID | Name
---------------
    1       Jim
    2       Bob

DeviceID | PersonID
-------------------
   40         1
   40         1
   70         2
   90         2

Gives me these results:
PersonID | DeviceID |    key
--------------------------------
    1         40      DeviceID1
    1         40      DeviceID2
    2         70      DeviceID1
    2         90      DeviceID2

But actually, because PersonID 1 has DeviceID 40 twice, I just need this:
PersonID | DeviceID |    key
--------------------------------
    1         40      DeviceID1
    2         70      DeviceID1
    2         90      DeviceID2

I tried adding DISTINCT to my query but it doesn't work in the first part and I'm not sure how to apply it within the partition bit...
Any help much appreciated!

Hituptony · Answer

WITH CTE AS
(
select
    p.[PersonID],
    d.[DeviceID],
    ROW_NUMBER() over(partition by p.PersonID order by d.DeviceID) as RowNum
from People p
    inner join Devices d
    on p.PersonID = d.PersonID
    where d.DeviceID is not null
), WITH CTE2 AS
(
SELECT * FROM CTE where RowNum = 1
)
SELECT *
,'DeviceID' + convert(char, ROW_NUMBER() over(partition by p.PersonID order by d.DeviceID)) as 'key'
FROM CTE2

I would split it up a bit into parts:
First isolate the rows you want where the deviceid is not null.
Second, grab only the rows you need.
Third, create the "key" and output results.

Gordon Linoff · Answer

Use dense_rank():
select distinct p.[PersonID], d.[DeviceID],
       concat('DeviceID',  dense_rank() over (partition by p.PersonID order by d.DeviceID)) as [key]
from People p join
     Devices d
     on p.PersonID = d.PersonID
where d.DeviceID is not null;

Notice that I also changed the code to use concat() rather than +.  Your conversion to a string is fraught with peril, because you don't include a length in the conversion.  In SQL Server, the default length varies by context.  Leaving out the length might work in this case.  But if you are in the habit of doing that, it might now work in another case.
Also, use single quotes only for string and date constants.  key is a bad name for a column because it is a SQL keyword.  If you do use it, use square braces or double quotes.

SQL pulling distinct values and creating an ID

2 Answers

Add your own answers!

Ask a Question