CODEWITHSUNDEEP
informaticainformatica-cloud
F.Marc
F.Marc

Reputation: 1

Choose random column value between rows with same ID

I'm a beginner with SQL and I have to do a saved query in Informatica Cloud for a connection with a SQL Server database.

I have a table where the rows with the same formId have the same columns except "possibleSalesman", which is a text column:

formId, email, possibleSalesman
1, email1, user1
1, email1, user2
1, email1, user4
2, email2, user2
3, email3, user3
3, email3, user1

What I need, is to get one row for each id and pick "possibleSalesman" randomly.

For example:

1, email1, user4
2, email2, user2
3, email3, user3

I found kind of a similar question but the solution wouldn't help me because there are a few restrictions in Informatica:

If someone could help me I would be very grateful!

Upvotes: 0

Views: 270

Answers (1)

Matt
Matt

Reputation: 14361

SELECT
    FormId
    ,Email
    ,possibleSalesMan
FROM
    (
    SELECT
      FormId
      ,Email
      ,possibleSalesMan
      ,ROW_NUMBER() OVER (PARTITION BY FormId ORDER BY NEWID()) AS RowNumber
    FROM
       TableName) t
WHERE
    t.RowNumber = 1

In SQL Server 2008+ you can use the ROW_NUMBER() window function and NEWID() to achieve a random order and then select the result where ROW_NUMBER() = 1.

Upvotes: 1

Related Questions