How to select multiple columns values same rows from single table

Question

I have a SQL Server table. Now this table has columns like primary key Id, A, B, C, D, E, F, G

Now I want to select rows from this table like this

A=A, B=B, C=C, D=D and G > 132

So I am trying to select rows from this table which rows A,B,C,D columns has same data and G column data > 132.

So how can I do that ? Thank you.

I tried this query but returning same Id rows

    SELECT TableA.Id,TableA.UserId,TableA.MaximumHp,TableA.Attack,TableA.Defense,TableA.SpAttack,TableA.SpDefense,TableA.Speed
FROM myTable as TableA
Inner Join myTable as TableB on 
TableA.MaximumHp = TableB.MaximumHp
  AND TableA.Attack = TableB.Attack
  AND TableA.Defense = TableB.Defense
    AND TableA.SpAttack = TableB.SpAttack
      AND TableA.SpDefense = TableB.SpDefense
        AND TableA.Speed = TableB.Speed
       AND TableA.Id != TableB.Id

SQL Server 2008 R2

I don't understand--A=A, B=B, C=C, and D=D will always be true. What are you trying to get to? Sample data would be helpful. — Eric
– Eric, Commented Dec 28, 2011 at 22:04
I am trying to select rows from this table which rows A,B,C,D columns has same data. — Furkan Gözükara
– Furkan Gözükara, Commented Dec 28, 2011 at 22:05

John Hartsock · Accepted Answer · 2011-12-28 22:09:35Z

4

Sounds like you want to join the table to itself

SELECT *
FROM Table t1
Inner Join Table t2 on t1.A = t2.A
  AND t1.B = t2.B
  AND t1.C = t2.C
  AND t1.D = t2.D
  AND t1.G > 132
  AND t1.ID <> t2.ID

answered Dec 28, 2011 at 22:09

John Hartsock

87.1k23 gold badges135 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Furkan Gözükara Over a year ago

Hello. I tried this but selecting same ID rows. t1 and t2 is same table here since i want to compare different rows of same table

John Hartsock Over a year ago

@MonsterMMORPG ... look again I am not comparing the same rows hence the last AND Clause t1.ID <> t2.ID

Furkan Gözükara Over a year ago

I don't get any error. Returning all of the rows at the table.

John Hartsock Over a year ago

lol....have you evaluated what you should be getting? make a simple test case and then verify that it works.

Furkan Gözükara Over a year ago

Yes i evaluated. I have many rows which are unique so the query should not return all of the rows at the table.

Isaac Fife · Accepted Answer · 2011-12-28 22:25:38Z

3

I THINK what you mean is duplicates. Tell me if this is what you are looking for.

SELECT [Table].A, [Table].B, [Table].C, [Table].D, [Table].E, [Table].F, [Table].G
FROM [Table] LEFT JOIN (SELECT A, B, C, D FROM [Table] 
GROUP BY A, B, C, D
HAVING count(*) > 1)
AS sub ON ([Table].A=sub.A) AND ([Table].B=sub.B) AND ([Table].C=sub.C) AND ([Table].D=sub.D)
WHERE G>132 and sub.A is not null;

This will give you all the rows where a,b,c, and D are equal to another row in the table...and G > 132

answered Dec 28, 2011 at 22:25

Isaac Fife

1,69013 silver badges15 bronze badges

11 Comments

Furkan Gözükara Over a year ago

Yes looking duplicates. Now going to check your answer thanks.

Furkan Gözükara Over a year ago

Hello. I tried but it returns all of the rows not duplicate ones : pastebin.com/qzMbkAZ2

Isaac Fife Over a year ago

If all of the rows in your table have a duplicate, then it will return all of them.

Furkan Gözükara Over a year ago

All the rows do not have duplicate. I made a minor mistake at where clause and now fixed it. Now trying again and Query is still running.

Isaac Fife Over a year ago

I hope this query isn't going into a game. Or if it is, I hope it's not going to be run while the game is in progress. I would only execute this when the game first loads or closes, otherwise it will take too long.

|

Collectives™ on Stack Overflow

How to select multiple columns values same rows from single table

2 Answers 2

5 Comments

11 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related