Top 15,000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Top 15,000

Author

Topic

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 10:10:15

I have a query that gives me 65,000 rows of results. I want to do a break down to get the query to show me the results in groups of 15,000. Does anyone know how to do this the easy way??

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 10:16:17

Are you using SQL 2005? If so, then check this out:

http://www.sqlteam.com/article/server-side-paging-using-sql-server-2005

If you have sql 2000, unfortunately, it is a bit more complicated.

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 10:29:27

Ok, that kinda helped. Does this mean I should great a temporary table? I know how to do that but I am still uncertain how to get it to show me results in groups of 15,000 rows. Here is what I did, but it doesn't work:
;With Groupresults
as
(SELECT Subscription.MDN,
Subscription.ESN,
Customer.LanguageID,
CustomerLoyalty.AgeBracket,
CustomerLoyalty.Nationality,
CustomerLoyalty.ChildCount,
Customer.Email,
Customer.Address1,
Customer.Address2,
Customer.City,
Customer.State,
Customer.ZipCode,
CustomerLoyalty.AlternatePhoneNumber,
Subscription.EffectiveDate,
CustomerLoyalty.DateEnrolled,
CustomerLoyalty.DateEligible,
CustomerLoyalty.Status,
SUM(CustomerLoyaltyPointsAdj.Points) as "Points Adjusted",
CustomerLoyalty.PointsEarned,
CustomerLoyalty.PointsRedeemed
FROM CustomerLoyalty
INNER JOIN Subscription
ON CustomerLoyalty.SubscriptionID = Subscription.SubscriptionID
INNER JOIN Customer
ON Subscription.CustomerID = Customer.CustomerID
LEFT OUTER JOIN CustomerLoyaltyPointsAdj
ON CustomerLoyalty.CustomerLoyaltyID = CustomerLoyaltyPointsAdj.CustomerLoyaltyID
WHERE Subscription.SubscriptionType IN ('CUSTOMER')
AND Subscription.StatusID IN ('ACT')
GROUP BY Subscription.MDN,
Subscription.ESN,
Customer.LanguageID,
CustomerLoyalty.AgeBracket,
CustomerLoyalty.Nationality,
CustomerLoyalty.ChildCount,
Customer.Email,
Customer.Address1,
Customer.Address2,
Customer.City,
Customer.State,
Customer.ZipCode,
CustomerLoyalty.AlternatePhoneNumber,
Subscription.EffectiveDate,
CustomerLoyalty.DateEnrolled,
CustomerLoyalty.DateEligible,
CustomerLoyalty.Status,
CustomerLoyalty.PointsEarned,
CustomerLoyalty.PointsRedeemed
)
SELECT RowNumber
FROM Groupresults
WHERE RowNumber BETWEEN 1 AND 15,000
ORDER BY RowNumber

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 10:46:46

When you say things "don't work", always tell us exactly what happens. Otherwise, we have no idea what you mean.

Anyway, you are returning just the row number column; you want all of the columns to be returned:

SELECT ~~RowNumber~~ *
FROM Groupresults
WHERE RowNumber BETWEEN 1 AND 15,000
ORDER BY RowNumber

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 11:06:29

Oops, sorry. This is what I get when I try to run the query:
Msg 102, Level 15, State 1, Line 54
Incorrect syntax near ','.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 11:15:43

You don't need the ; symbol before the WITH if there is no T-SQL statement before it. Try removing it.

Also, are you sure you are using SQL 2005?

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 11:34:24

Yes, it is SQL 2005. I removed the ';' and the ',' and now I am getting:
Msg 207, Level 16, State 1, Line 54
Invalid column name 'RowNumber'.
Msg 207, Level 16, State 1, Line 54
Invalid column name 'RowNumber'.
Msg 207, Level 16, State 1, Line 55
Invalid column name 'RowNumber'.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 11:56:00

Please post ALL of your code. What , did you remove??

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 11:59:19

Oh, I see it, it's here:

>>WHERE RowNumber BETWEEN 1 AND 15,000

Get rid of that comma, it is not valid syntax for a numeric value.

Also, read the article again carefully -- you did not add a ROWNUMBER column to your SELECT, so there is nothing to filter on.

Finally, test new techniques on small, simple sets of data -- don't just copy and paste your complicated SQL statement to see if it works. Start with a very simple SELECT from a small table, practice returning rows 1-3, then 4-6, etc, and really make sure you understand *exactly* what you are doing. Only after you really get it should you attempt to implement this on your more complicated code.

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 14:33:08

I did take the ',' out from the 15,000, and the error message that I provided above was the result. I re-read the article, and I guess I am just really confused on how to put it into my query. Row_Number () function does what? and how does it recognize 'rownumber' when you ask it to SELECT it? I am sorry if I am being way too confusing. Do you know of a way to get the query to give me the top 15000, and then the next 15000?? Does that make sense?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 15:06:17

Row_number() simply provides a number for each row in the SELECT statement. Again, I can only suggest that you carefully read the article -- I really cannot explain it any clearer than the article does ... And, more importantly, practice on a small, simple table with only a few rows in it until you get the hang of it and are sure you understand how it works. If you can return rows 1-5,6-10 and then 11-15 of a small, simple 15 row table, you can apply the exact same concept on a table with thousands of rows.

Maybe I should step back a bit: what do you mean by "you want to show the results in groups of 15,000" ? Can you explain exactly and specifically what you are trying to do? An example would be helpful.

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 15:38:07

The best way for me to explain it is, let say, for example, I run a query and it returns 250,000 rows of results. I want to take those results and group them in groups of 15,000. Like, take the top 15,000 results, group them together, then the next, and so on, and so forth. Does that make it any clearer??

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 15:48:39

No, it doesn't. Define what you mean by "group them together". A single resultset in a database has no groups -- it just contains data. At your front-end presentation layer, you can *present* that data by *formatting* it certain ways, and you can indent things or use large fonts or headings or insert blank lines or page breaks or horizontal rules to *present* groups on a page or a webpage, but that has nothing to do with a database. A database just returns a simple grid of data consisting of rows and columns, nothing else.

It is impossible to help you without knowing specifically what you are trying to do. Chances are, though, this is not a SQL question at all, but a presentation question. I hope this makes sense. As I asked before, an example would be very helpful -- just use a simple, small scale example that is easy to follow and that covers the logic you need.

- Jeff
http://weblogs.sqlteam.com/JeffS

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 16:22:04

I am not sure how to explain this very well, apparently. If you ran a query looking for results from two different databases and instead of using UNION, you just leave a blank line between the two, such as:
SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE CallDate >= '02/19/2007' and CallDate < '02/26/2007'
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group by dateadd(day, datediff(day, 0, CallDate), 0)

SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE CallDate >= '02/19/2007' and calldate < '02/26/2007'
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group by dateadd(day, datediff(day, 0, CallDate), 0)

It gives you two sets of results. I would like to be able to run one query to where it will show me 15,000 rows of the 250,000 rows in different result sets like it would with the above query. I am not sure if that helped at all or just made things more confusing.

confuzed04
Starting Member

39 Posts

Posted - 2007-06-26 : 16:36:26

Is it possible to use 'TOP (15000)' and something else so that it gives me the top 15,000 and then the next 15000 as separate results?? like a Next Top (15000)?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 19:55:29

quote:
Originally posted by confuzed04

I am not sure how to explain this very well, apparently. If you ran a query looking for results from two different databases and instead of using UNION, you just leave a blank line between the two, such as:
SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE CallDate >= '02/19/2007' and CallDate < '02/26/2007'
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group by dateadd(day, datediff(day, 0, CallDate), 0)

SELECT dateadd(day, datediff(day, 0, CallDate), 0) as CallDate,sum(PaidBalCost), sum(BonusBalCost), sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE CallDate >= '02/19/2007' and calldate < '02/26/2007'
AND (((CONVERT(varchar, CallDate, 108) Between '07:00:00' AND '20:59:59'))
AND DATEPART(weekday, CallDate) in (2,3,4,5,6))
Group by dateadd(day, datediff(day, 0, CallDate), 0)

It gives you two sets of results. I would like to be able to run one query to where it will show me 15,000 rows of the 250,000 rows in different result sets like it would with the above query. I am not sure if that helped at all or just made things more confusing.

WHY???? Why do you think you want different result sets like that? For what purpose? TO display it a certain way on a report or web page? Why would you want to complicate things and return a random number of result sets in a stored procedure? What application is executing this stored procedure?

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-26 : 19:56:18

quote:
Originally posted by confuzed04

Is it possible to use 'TOP (15000)' and something else so that it gives me the top 15,000 and then the next 15000 as separate results?? like a Next Top (15000)?

That's what the article link that I showed you does. You can select row numbers 1-15000 the first time, then 15001-30000 the second time, and so on.

Well, if you finally decide to be specific about what you need, let me know. Otherwise, this is just going in circles.

- Jeff
http://weblogs.sqlteam.com/JeffS

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 02:44:23

Jeff,

My best guess is that the OP wants a two resultsets: the first returns rows 1-15000, the second 15001-30000.

I cannot begin to imagine how this is different to just returning 30,000 rows ...

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-27 : 04:07:34

This highly sounds nothing but pagination

Question to the questioner

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-06-27 : 06:04:27

"This highly sounds nothing but pagination

Where do you want to show data?"

15,000 rows? Big page size!

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-27 : 08:15:47

Until the OP starts answering the questions we ask (including all of the ones I've been asking over and over) we'll never get anywhere. It's a shame, I'd love to help, and I know we can, and I also know that it is much simpler than what the OP is trying to do, but we'll never be able to help if we don't get any details.

The OP is convinced that he needs multiple result sets for some reason, yet he won't (or can't) explain why!

It's like if a car has a flat tire, and for some reason you think the way to fix it is by removing the steering wheel. So you ask a mechanic "how do you remove the steering wheel?" instead of asking "how to I fix a flat tire?".

- Jeff
http://weblogs.sqlteam.com/JeffS

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources