Prev: Preaching does not help
Next: Select a row in a group with a column that has the maximum value
From: NetNewbie on 12 May 2010 16:20
Hi

I want to write a query to select columns from 3 tables (T1=120 million
rows, t2=200 M and T3 = 9.5 M) grouped by a column in table T3.

select count(*), T3.C1
from T1 <..>
Join T2 <..>
Join T3 <..>
group by T3.C1

The question is it possible for me to select top 10 rows or a sample of rows
from each group or rows in the above group by query. Should I try using the
Over() clause with partition?

Is is possible?

Thanks
From: Erland Sommarskog on 12 May 2010 18:14
NetNewbie (NetNewbie(a)discussions.microsoft.com) writes:
> I want to write a query to select columns from 3 tables (T1=120 million
> rows, t2=200 M and T3 = 9.5 M) grouped by a column in table T3.
>
> select count(*), T3.C1
> from T1 <..>
> Join T2 <..>
> Join T3 <..>
> group by T3.C1
>
> The question is it possible for me to select top 10 rows or a sample of
> rows from each group or rows in the above group by query. Should I try
> using the Over() clause with partition?

Not really sure what you are asking for, but it sounds like you
are looking for:

WITH numbered AS (
SELECT ..., rowno = row_number() OVER(PARITION BY T3.C1
ORDER BY whatevrcol)
FROM T1
JOIN T2
JOIN T3
)
SELECT ...
FROM numbered
WHERE rowno <= 10
ORDER BY T3C1, whatevercol


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Preaching does not help
Next: Select a row in a group with a column that has the maximum value