[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 72
  • Last Modified:

How can I limit the number of rows I'm getting in my recordset?

When I do this:

select chargegroupid from txn where accountid = 9949796 group by chargegroupid having max(txn.created) > DATEADD(DAY, -45, GETDATE())

I get over 200 rows. And that's tame. I've got some situations where I've got over 800.

On the other hand, when I do this:

select created, chargegroupid from txn where accountid = 9949796 and created > DATEADD(DAY, -45, GETDATE())

I get 70 rows, which is the recordset that I need.

How do I change the original query so I'm getting my 70 rows as opposed to the 200?
0
brucegust
Asked:
brucegust
  • 2
2 Solutions
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
SELECT TOP 50 ... will get the first 50 rows based on the where clause.  Change 50 to 5 or 77 or whatever number you want.
0
 
lcohanDatabase AnalystCommented:
Where is that "original query" stored? All you have to do is to replace is with the one that returns 70 rows if that's what you need and expect right?
Besides that for large record sets returned from SQL you should be implementing "paging" via SQL Stored procedures as described here:

http://social.technet.microsoft.com/wiki/contents/articles/23811.paging-a-query-with-sql-server.aspx
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
I see, never mind.  Have you looked over the data returned to see what, exactly is being returned that's extra?
0
 
brucegustPHP DeveloperAuthor Commented:
I DBA sitting next to me and he just helped me out.

Bottom line: The original query needs to remain unchanged because even though there might be, what appears to be an exorbitant number of rows, it's still accurate because of the original charge. In other words, while the most recent payment may be September 23, 2016, if the original charge was back in 2013, I'm going to see those results.

Thanks for weighing in!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now