T-SQL Subquery Question

Question

i have two queries. For each tuple of query1 i want to run query2. i dont want to use cursors. i tried several approaches using subqueries.

query1:

select 
    distinct 
    category, 
    Count(category) as CategoryCount
from 
    mytable 
group by 
    category

query2:

select
   top 3 
   Text,
   Title,
   Category
from
    mytable
where
    Category = '1'

Category = '1' is a sample. the value should come from query1

you always need top 3 form mytable for each category

Pranay Rana
– Pranay Rana

2010-05-19 10:24:26 +00:00
Commented May 19, 2010 at 10:24 — Pranay Rana
– Pranay Rana, Commented May 19, 2010 at 10:24

Chris Bednarski · Accepted Answer · 2010-05-19 10:58:11Z

1

Try this

WITH TBL AS
(
SELECT TEXT, TITLE, CATEGORY,
       COUNT(*) OVER(PARTITION BY CATEGORY) AS CATEGORYCOUNT,
       ROW_NUMBER() OVER(PARTITION BY CATEGORY ORDER BY (SELECT 0)) AS RC
FROM MYTABLE
)
SELECT TEXT, TITLE, CATEGORY, CATEGORYCOUNT
FROM TBL
WHERE RC <= 3
ORDER BY CATEGORY

edited May 19, 2010 at 10:58

answered May 19, 2010 at 10:28

Chris Bednarski

3,43427 silver badges34 bronze badges

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

6 Comments

Chris Bednarski Over a year ago

which version of SQL are you running? This will not work on 2000. Only 2005+

NickD Over a year ago

but without the WHERE RC <= 3 exactly what i want.

NickD Over a year ago

select @@version returns Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

NickD Over a year ago

it looks like the distinct is applied and the top is ignored.

Chris Bednarski Over a year ago

@Nick: You should get a maximum of 3 rows per category with this query. However, there could be less

|

Collectives™ on Stack Overflow

T-SQL Subquery Question

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related