how to query data?

Potter123 260 Reputation points
2023-04-10T07:26:45.49+00:00

Hello. I have the following table. Now I need to query out the records in each group where value1 and value2 have the same value sorted from smallest to largest. For example, according to the table below, I can get (1, 12, 20). create table sourcetable(groupid int,value1 int,value2 int) insert into sourcetable values(1,10,25),(1,50,15),(1,12,20),(1,15,30) Thanks.

SQL Server | Other
{count} votes

Accepted answer
  1. Anonymous
    2023-04-10T07:31:09.41+00:00

    Hi @--
    If I understand correctly, you can try this query.

    ;with CTE as(
      select *,row_number()over(partition by groupid order by value1) as num1,
             row_number()over(partition by groupid order by value2) as num2 from sourcetable)
    select groupid,value1,value2 from CTE where num1 = num2;
    

    Best regards, Percy Tang

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.