how can I get top(N) rows for each group
-
Hello. I have the following problem:I need to retreive top(N)rows for each group in a single query. Here is an example of what I would like to do: select top(10) with ties table1.field1, table1.field2, table2.field1 from table1 inner join table2 on table1.some_field = table2.same_field group by table1.field1 order by table1.field1 As you can see, this query will return only 10 rows. What I need is 10 rows for each group (table1.field1). Can anyone help?
-
Hello. I have the following problem:I need to retreive top(N)rows for each group in a single query. Here is an example of what I would like to do: select top(10) with ties table1.field1, table1.field2, table2.field1 from table1 inner join table2 on table1.some_field = table2.same_field group by table1.field1 order by table1.field1 As you can see, this query will return only 10 rows. What I need is 10 rows for each group (table1.field1). Can anyone help?
Try something like this: SELECT t1.Field1, t1.Field2, t2.Field3 FROM Table1 t1 JOIN Table2 t2 ON t2.JoinField = t1.JoinField WHERE t2.IdField IN ( SELECT TOP(10) t3.IdField FROM Table2 t3 WHERE t3.JoinField = t2.JoinField ORDER BY t3.IdField DESC ) It is called a correlated subquery.