Return two rows from SQL table with a difference in values [duplicate]

Question

iam trying to return 2 rows from table that have a difference in values, not being an SQL wise man i am stuck any help would be appreciated :-)

TABLE A:

NAME    DATA
Oscar   HOME1
Jens    HOME2
Will    HOME1
Jeremy  HOME2
Al      HOME1

Result, should be 2 random rows with a difference in DATA value

NAME   DATA
Oscar  HOME1
Jeremy HOME2

Anyone?

Answer 1

Easy way to have random data.

;with tblA as (
select name,data,
row_number() over(partition by data order by newid()) rn
from A
)
select name,data
from tblA
where rn = 1

Answer 2

Couuld be you need

select * from my_table a
inner join my_table b on a.data !=b.data
where a.data in ( SELECT data FROM my_table ORDER BY RAND() LIMIT 1);

For your code

SELECT * 
FROM [dbo].[ComputerState] as a
INNER JOIN [dbo].[ComputerState] as b ON a.ServiceName != b.ServiceName 
WHERE a.ServiceName IN ( 
     SELECT  top 1  [ServiceName] FROM [dbo].[ComputerState] 
);

Answer 3

If the question is really this simple, you can use an aggregate such as MAX() or MIN() to grab one row for each different DATA:

SELECT   MAX(NAME), DATA
FROM     TABLE_A
GROUP BY DATA

Of course, if any other variables are introduced to the requirements, this may no longer work.

Answer 4

;WITH cteA AS (
    SELECT
       name
       ,data
       ,ROW_NUMBER() OVER (PARTITION BY data ORDER BY NEWID()) as DataRowNumber
       ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY NEWID()) as RandomRowNumber
    FROM
       A
)

SELECT *
FROM
    cteA
WHERe
    DataRowNumber = 1
    AND RandomRowNumber <= 2

This Expands on @AlexKudryashev 's answer a little.

;with tblA as (
select name,data,
row_number() over(partition by data order by newid()) rn
from A
)
select name,data
from tblA
where rn = 1

The only issue with what he had Is that the number of Rows where rn = 1 will be depended on the COUNT(DISTINCT data) so it could lead to more than 2 results. To fix one could add a SELECT TOP 2 clause but it might not be fully random as results at that point as it will be dependent on the ordinal results of how SQL optimizes the query which is likely to be consistent. To get truly random add a second random row number and limit the results to the top 2 of those.