Return two rows from SQL table with a difference in values

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?

Even better if the code didnt require static entries like HOME1 <> HOME2 — dahund, Jun 21 '16 at 19:52
Should this question belong on code-golf? http://codegolf.stackexchange.com/ — ebyrob, Jun 21 '16 at 20:07

score 2 · Answer 1 · answered Jun 21 '16 at 19:56

2

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

answered Jun 21 '16 at 19:56

Alex Kudryashev

9,120
3
27
36

Wouldn't it be `where rn <= 2` as the OP wants 2 rows (or `IN (1,2)`)? – Igor Jun 21 '16 at 19:58
@Igor No. See `partition by` – Alex Kudryashev Jun 21 '16 at 19:59
This assume OP has Sql Server. – Juan Carlos Oropeza Jun 21 '16 at 20:01
Brilliant, thanks. @JuanCarlosOropeza - IMO you have to assume something as the OP gives very little information. If the OP wanted a server specific solution they should have included that in their question. – Igor Jun 21 '16 at 20:03
@Igor I may think the other way, general rule if you assume you are probably wrong. So dont waste your time asking the wrong question. Is better ask for details. Is obvious OP is new user so teach him how make a better question instead. – Juan Carlos Oropeza Jun 21 '16 at 20:07
There does need to be SELECT TOP 2 however though because the partition is on data and in the example data that would create 3 rows with row_number = 1. I addition that could end up always choosing the same data combination or preference – Matt Jun 21 '16 at 20:12
@Matt `newid()` is very good random number generator. ;) – Alex Kudryashev Jun 21 '16 at 20:18
I know it is and I use it as such too! I was thinking their were 3 distinct values in the data column. But it is because I wasn't looking at the word data as a column name.... my bad – Matt Jun 21 '16 at 20:36

score 0 · Answer 2 · answered Jun 21 '16 at 19:50

0

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.

answered Jun 21 '16 at 19:50

Aaron Dietz

10,137
1
14
26

1

how is this random? – devlin carnate Jun 21 '16 at 19:51
@devlincarnate Good point, I read the top part with a focus on simply having different `DATA` values. If it has to be truly random then this won't work. – Aaron Dietz Jun 21 '16 at 19:52
Column 'DATA' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. you know what this is? – dahund Jun 21 '16 at 20:15
@dahund `DATA` is right there in: `GROUP BY DATA` – ebyrob Jun 21 '16 at 20:16

ScaisEdge · Accepted Answer · 2016-06-21T20:44:12.810

0

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] 
);

edited Jun 21 '16 at 20:44

answered Jun 21 '16 at 19:50

ScaisEdge

131,976
10
91
107

how does this return 2 random rows? – devlin carnate Jun 21 '16 at 19:53
@devlincarnate correct .. i have update the answer with random data – ScaisEdge Jun 21 '16 at 19:56
This returns one row with 2 sets of values in it. The question asks for the values in two separate rows. – ebyrob Jun 21 '16 at 19:59
BY "inner join table b" you mean "inner join table a" as it is same table= – dahund Jun 21 '16 at 19:59
by inner join b i mean the inner join with the same table .. exactly .. in sql the tables are tables .. same or not same are tables .. and you can use them like you explicit in the condition .. – ScaisEdge Jun 21 '16 at 20:02
@dahund scaisEdge called the table `table` instead of `A` as the question outlined it. So, in this answer, `a` and `b` are just table aliases in the query. – ebyrob Jun 21 '16 at 20:15
@ebyrob correct .... table a mean a is the alias of table .. a.data mean the colulmn data of the table with alias a – ScaisEdge Jun 21 '16 at 20:18
So this ( SELECT data FROM table ORDER BY RAND() LIMIT 1); what is table? – dahund Jun 21 '16 at 20:21
SELECT * FROM [dbo].[ComputerState] INNER JOIN [dbo].[ComputerState] ON [dbo].[ComputerState].ServiceName != [dbo].[ComputerState].ServiceName WHERE [dbo].[ComputerState].ServiceName IN ( SELECT [ServiceName] FROM [dbo].[ComputerState] ORDER BY RAND() LIMIT 1) Gives me "Incorrect syntax near 'LIMIT'." – dahund Jun 21 '16 at 20:23
@dahund in this subselect there are not alias so the table is "table".. anyway i have change the answer . with my_table instead of table .. seeem you don't know the alias .. – ScaisEdge Jun 21 '16 at 20:23
I have update the answer setting the alias for your code .. hope useful – ScaisEdge Jun 21 '16 at 20:27
yep getting there :-) looks better now getting the tables next to each other, but give me this "Incorrect syntax near 'LIMIT'." if i add "ORDER BY RAND() LIMIT 1" i maybe should have told it is SQL :-) – dahund Jun 21 '16 at 20:41
SELECT TOP(1) * and removed LIMIT and order – dahund Jun 21 '16 at 20:43
@dahund yes ... is sqlserver and not mysql .. i have update the answer – ScaisEdge Jun 21 '16 at 20:44
Perfect this saves me from alot of C# code, merci :-) – dahund Jun 21 '16 at 20:46
hey again, is there any chance this could return table in old order? since my dataset now has on row with [servicename, computername, servicename1, computername1] – dahund Jun 22 '16 at 21:30
i suggest you of post a new question correctly described .. in this way all the community can help you .. – ScaisEdge Jun 22 '16 at 21:34

score 0 · Answer 4 · answered Jun 21 '16 at 20:22

;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.

Return two rows from SQL table with a difference in values

4 Answers4