Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ORDER BY and TOP in a subquery
- Thread starter Earth
- Start date
When you create a view, derived table or subquery, you cannot put ORDER BY unless you put TOP in the query, eg.
USE pubs
GO
CREATE VIEW test
AS
SELECT *
FROM authors
ORDER BY au_id
This causes an error message 1003. I was not able to find the right explanation about this, and I very rarely use ORDER BY in a subquery.
Best regards,
Husein
USE pubs
GO
CREATE VIEW test
AS
SELECT *
FROM authors
ORDER BY au_id
This causes an error message 1003. I was not able to find the right explanation about this, and I very rarely use ORDER BY in a subquery.
Best regards,
Husein
tlbroadbent
MIS
Order By is only applied to the final result except in the case of the TOP statement. Therefore, allowing Order By in a sub query without a TOP statement serves no purpose. Why would you want to order the result of a sub-query? Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #6
I wanted to order the result of my sub-query so that I could effectively move to the "previous n rows" - in a method for paging in a web-interface that can return hundreds of rows.
Parameters: @numRows - the number of rows to be returned
@dateFrom & @dateTo - a date range that the returned rows must be within
@dateEdge - a date on the cusp of what was returned by the last query call (specifying this figure is done by the calling class). Rough code:
SELECT *
FROM (
SET ROWCOUNT @numRows
SELECT (Some select stuff that works and aggregates data based on date - returns one row for each date)
FROM tblBalancesAsAt
WHERE AsAtDate >= @dateFrom
and AsAtDate <= @dateTo
and AsAtDate < @dateEdge
GROUP BY AsAtDate
ORDER BY AsAtDate DESC
)
ORDER BY AsAtDate ASC
The logic behind this query is (with example):
1. Run the inner query to find rows with dates within the specified range, and take out the n rows immediately preceding the "date cusp" (ie. When the resulting rows are ordered by date DESC, the "top" n will be the ones immediately preceding the date cusp.
2. Return these rows in the right (ASC) order!
I couldn't find a way to implement this however. Any ideas?
Thanks.
Parameters: @numRows - the number of rows to be returned
@dateFrom & @dateTo - a date range that the returned rows must be within
@dateEdge - a date on the cusp of what was returned by the last query call (specifying this figure is done by the calling class). Rough code:
SELECT *
FROM (
SET ROWCOUNT @numRows
SELECT (Some select stuff that works and aggregates data based on date - returns one row for each date)
FROM tblBalancesAsAt
WHERE AsAtDate >= @dateFrom
and AsAtDate <= @dateTo
and AsAtDate < @dateEdge
GROUP BY AsAtDate
ORDER BY AsAtDate DESC
)
ORDER BY AsAtDate ASC
The logic behind this query is (with example):
1. Run the inner query to find rows with dates within the specified range, and take out the n rows immediately preceding the "date cusp" (ie. When the resulting rows are ordered by date DESC, the "top" n will be the ones immediately preceding the date cusp.
2. Return these rows in the right (ASC) order!
I couldn't find a way to implement this however. Any ideas?
Thanks.
tlbroadbent
MIS
Have you seen the following paging articles?
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Status
Similar threads
- Locked
- Question
- Locked
- Question