[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-11-07
4
Medium Priority
?
68 Views
Last Modified: 2016-11-07
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
Comment
Question by:brucegust
  • 2
4 Comments
 
LVL 97

Expert Comment

by:Lee W, MVP
ID: 41877658
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
 
LVL 40

Accepted Solution

by:
lcohan earned 1000 total points
ID: 41877660
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
 
LVL 97

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 1000 total points
ID: 41877661
I see, never mind.  Have you looked over the data returned to see what, exactly is being returned that's extra?
0
 

Author Comment

by:brucegust
ID: 41877696
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question