| Author |
Topic |
|
lprassanth
Starting Member
3 Posts |
 Posted - 2009-02-19 : 04:21:49
|
| Hi,This is the problem I face. Any help greatly appreciated :)Table T:Column A Column B 1 1 1 2 1 3 2 4 2 5 2 6 3 5 3 4 3 3Let's say that the above is what i have in my table. This is a simplified version and the real table has millions of records. Now I need to get 2 sample records from the table for every value of column A. For more clarity...Eg:select A, B, Count(*) From T Where A = '1' Group By 1, 2Sample 2;This will only get me 2 samples from the table, when A = 1. I would need 2 samples for each value of A (i.e. for value 1, 2, and 3) - which translates into returning 6 rows.Could this be done in a single query? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-02-19 : 04:24:09
|
| http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.aspxMadhivananFailing to plan is Planning to fail |
 |
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-19 : 04:26:03
|
| select * from (select row_number(partition by columna order by columna)as rid,* from tablet)t where rid <=2 |
|
|
|
lprassanth
Starting Member
3 Posts |
Posted - 2009-02-19 : 04:58:15
|
| @ the above 2 replies - will it work in Teradata?Thanks both of you.. |
|
|
|
lprassanth
Starting Member
3 Posts |
Posted - 2009-02-19 : 05:20:08
|
| @ MadhivananThanks a ton for the link..i still don't quite get the req. result in Teradata..for solution 1) Teradata throws an error that "Top N" is not supported in a sub queryand as for solution 3)i dont think row_number is supported in Teradata either :(for once i despise Teradata :)any other alternatives...? |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2009-02-19 : 05:42:45
|
| Doesn't Teradata have a sql engine of its own ?? The queries here might/might not work there. Might as well post this in a Teradata forum. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-02-20 : 02:47:18
|
quote:
Originally posted by lprassanth
@ the above 2 replies - will it work in Teradata?Thanks both of you..
Post your question at Teradata forumMadhivananFailing to plan is Planning to fail |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-02-20 : 02:55:53
|
quote:
Originally posted by bklr
select * from (select row_number(partition by columna order by columna)as rid,* from tablet)t where rid <=2
It is point 3 on the link  MadhivananFailing to plan is Planning to fail |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-02-20 : 03:29:56
|
quote:
Originally posted by madhivanan
quote:
Originally posted by bklr
select * from (select row_number(partition by columna order by columna)as rid,* from tablet)t where rid <=2
It is point 3 on the link MadhivananFailing to plan is Planning to fail
hai madhivanan,i haven't seen the link i just given the query ..... |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-02-20 : 04:55:15
|
quote:
Originally posted by bklr
quote:
Originally posted by madhivanan
quote:
Originally posted by bklr
select * from (select row_number(partition by columna order by columna)as rid,* from tablet)t where rid <=2
It is point 3 on the link MadhivananFailing to plan is Planning to fail
hai madhivanan,i haven't seen the link i just given the query .....
No problem MadhivananFailing to plan is Planning to fail |
|
|
|
|
How to get sample data of each Group By type?