CODEWITHSUNDEEP
sql-server
Revathi Vijay
Revathi Vijay

Reputation: 1308

How to select the value from the table based on category_id USING SQL SERVER

How to select the value from the table based on category_id?

I have a table like this. Please help me.

Table A

ID Name category_id
-------------------
 1  A       1
 2  A       1
 3  B       1
 4  C       2
 5  C       2
 6  D       2
 7  E       3
 8  E       3
 9  F       3

How to get the below mentioned output from table A?

ID Name category_id
--------------------
 1  A     1
 2  A     1
 4  C     2
 5  C     2
 7  E     3
 8  E     3

Upvotes: 2

Views: 273

Answers (2)

MuhammadWaseem
MuhammadWaseem

Reputation: 46

Kindly run this query It really help You Out.

SELECT  tbl.id,tbl.name, tbl.category_id FROM TableA as tbl WHERE 
tbl.name IN(SELECT tbl2.name FROM TableA tbl2 GROUP BY tbl2.name HAVING Count(tbl2.name)> 1)

Code select all category_id from TableA which has Name entries more then one. If there is single entry of any name group by category_id then such data will be excluded. In above example questioner want to eliminate those records that have single Name entity like wise category_id 1 has name entries A and B among which A has two entries and B has single entry so he want to eliminate B from result set.

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Give a row number for each row based on group by category_id and sort by ascending order of ID. Then select the rows having row number 1 and 2.

Query

;with cte as (
    select [rn] = row_number() over(
        partition by [category_id]
        order by [ID]
    ), *
    from [your_table_name]
)
select [ID], [Name], [category_id]
from cte
where [rn] < 3;

Upvotes: 2

Related Questions