Returning TOP rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Returning TOP rows

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 10:29:44

I have a table as follows:

Date ID Value

where the Date is in months and years. I can return the top 1000 values from this table for a given date as:

select top 1000 Value, Date, ID from mytable where Date = '1990-01-01'

(or whatever Date I may choose). However, I'm trying to return the top 1000 values for every month in my table. Can anyone show me how to do this? I have a feeling a group by is needed here somewhere, but I'm not sure where.

Any help much appreciated!

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 10:32:45

Sure

http://weblogs.sqlteam.com/brettk/archive/2005/02/10/4153.aspx

Brett

8-)

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 11:11:01

Thanks. From the reading I get, I'm using a query as follows:

select a.Value, a.Date, a.ID

from mytable a

where (select count(*) from mytable b
where b.ID = a.ID
and a.Value <=b.Value) <= 1000
order by a.Date asc, a.Value desc

does this seem to make sense?

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 12:00:51

Actually, this isn't working. All I'm getting is the entire table all over again (which seems to make sense, re-reading the query, actually).

Any ideas what I'm doing wrong here? I thought I was using what was at the crux of that blog, but perhaps my tables are different?

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-09 : 12:20:46

Did you cut and paste the examples to see how they work?

Brett

8-)

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 12:43:04

Looking at the 'showplan'...hopefully I'll beat this into my head before long.

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-12 : 17:47:12

Hi Brett,

Thank you very much for your assistance, but I'm afraid I can't make heads or tales of it. Is there any way you might be able to phrase your answer in a different way or narrow down what I should be focusing on? I'm really little more than a SQL beginner, I'm afraid.

CLages
Posting Yak Master

116 Posts

Posted - 2005-05-12 : 19:52:55

Try this and fix it up for your neeeds
C. Lages

declare @tabela table (data smalldatetime)

insert into @tabela (data) VALUES ('05/12/2005')
insert into @tabela (data) VALUES ('05/13/2005')
insert into @tabela (data) VALUES ('05/01/2005')
insert into @tabela (data) VALUES ('05/17/2005')
insert into @tabela (data) VALUES ('05/23/2005')
insert into @tabela (data) VALUES ('05/30/2005')

insert into @tabela (data) VALUES ('03/05/2004')
insert into @tabela (data) VALUES ('03/12/2004')
insert into @tabela (data) VALUES ('03/07/2004')
insert into @tabela (data) VALUES ('03/27/2004')
insert into @tabela (data) VALUES ('03/02/2004')

select * from @tabela

select * from
(select top 2 * from @tabela where month(data) = 05 and year(data) = 2005 group by data) xx

union all

select * from
(select top 2 * from @tabela where month(data) = 03 and year(data) = 2004 group by data) yy

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-13 : 15:12:37

Thanks. I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources