Solved

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

Posted on 2016-11-07
4
54 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 95

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 39

Accepted Solution

by:
lcohan earned 250 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 95

Assisted Solution

by:Lee W, MVP
Lee W, MVP earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now