Solved

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

Posted on 2016-11-07
4
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 96

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 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 96

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 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