Solved

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

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 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