Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2003
    Posts
    65

    Unanswered: Selecting a Range (SP)

    Hey, I'm trying to create a Stored Procedure that will select a range of news items.

    I am arranging the news items by a column called Written (which is a DATETIME column) I would like to order the entries by date, then return the entries between in 'index' points @startIndex and @endIndex.

    After a lot of searching and testing, I have come to the method below. I have no idea if it's a good way of going about it or not, so feel free to tell me I'm doing it all wrong. But either way, I'm stuck. This complains about my formatting at the TOP statements, and I'm not sure how to fix it.

    CREATE PROCEDURE news_display
    @startIndex INT,
    @endIndex INT
    AS
    DECLARE @top int;
    SET @top = (@endIndex-@startIndex);
    SELECT TOP @top *
    FROM News
    WHERE NewsID NOT IN
    (SELECT TOP @startIndex-1 n.NewsID FROM News n Order By Written) AND NewsID != 1
    Order By Written
    GO

    Any Advice or suggestions would be GREATLY appreciated,
    Thanks,
    -Ashleigh
    -Ashleigh

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    of course you can convert it to dynamic sql (not recommended) or you can look into SET ROWCOUNT @TOP prior to issueing your select. i haven't tried it but it may help.

  3. #3
    Join Date
    Sep 2003
    Posts
    65
    Hm, ok, I'll look into the @Rowcount,

    at the moment though, I just want to see it working, lol. The error messages are starting to do me in ;-(.

    How can I go about getting the code I have above to work?
    (somthing, is wrong with my syntax, and I don't know how to fix it, lol)
    -Ashleigh

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Unfortunatly, you can't supply a parameter or variable to the TOP clause. It has to be hard-coded, or you must construct your query on an as-needed basis with the TOP values you require (the dynamic sql solution that ms_sql_dba mentions).

    Fortunatley, I think you may be able to avoid you the TOP clause altogether. See if this doesn't give you the results you want:

    CREATE PROCEDURE news_display (@startIndex INT, @endIndex INT)
    AS
    select *
    from News
    inner join
    (select News.Written, count(*) as NewsIndex
    from News
    inner join News News2 on News.Written >= News2.Written
    group by News.Written) NewsIndexes
    on News.Written = NewsIndexes.Written
    where NewsIndexes.NewsIndex between @StartIndex and @EndIndex

    This assumes your Written fields are unique values.

    blindman

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    based on the fact that your are trying to retrieve n number of rows from News with NewsID starting with n1 and ending with n2 and not equal to 1, - i think using SET ROWCOUNT @top would do the trick for you.

    the rest of the query is almost unchanged:

    SET ROWCOUNT @top
    SELECT *
    FROM News
    WHERE NewsID between @startIndex and @endIndex
    AND NewsID != 1
    Order By Written

  6. #6
    Join Date
    Sep 2003
    Posts
    65
    Thanks for your help guys.

    I think I'll use blindmans solution. I'm a bit of a novice sql programmer, so I'm not 100% sure, but I'm pretty sure that method is exactly what I'm looking for.

    I'm running a little late (ops) so I don't have time to test it right now, but I'll test it first thing tonight. Thanks a heap for that (Written is unique). Awesome.

    -Ashleigh
    -Ashleigh

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, not 100%. I see I left out some of your criteria, but if you have any more questions or problems let me know.

    blindman

  8. #8
    Join Date
    Sep 2003
    Posts
    65
    Originally posted by blindman
    Well, not 100%. I see I left out some of your criteria, but if you have any more questions or problems let me know.

    blindman
    Hey, I've got it working, almost, perfectly, lol. This is what I have right now:

    CREATE PROCEDURE news_display (@startIndex INT, @endIndex INT)
    AS
    select *
    from News
    inner join
    (select News.Written, count(*) as NewsIndex
    from News
    inner join News News2 on News.Written >= News2.Written
    group by News.Written) NewsIndexes
    on News.Written = NewsIndexes.Written
    where NewsIndexes.NewsIndex between @StartIndex and @EndIndex AND NewsID != 1
    GO


    The problem I'm having is, The old messages are first, and the new messages are last. So I need to add order by News.Written Desc somewhere.

    I tried adding it to the end, but the articles are then numbered backwards. I *could* get it to work using this method if I also stored how many articles there were, but I firgure there must be a way to say (in the group by statement?) to order them descending? I tried putting an order by statement there, but that obviously isn't right. And I'm afraid the SQL book I bought (sql bible), while useful, doesn't have the best Index in the world, so I'm stuck again, lol...

    Thanks for any help you can give,
    -Ashleigh
    -Ashleigh

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    When you define an ORDER BY CLAUSE, you can specify whether you want ascending order (ORDER BY YOURFIELD ASC) or descending order (ORDER BY YOURFIELD DESC). Ascending is the default it you do not explicitly specify a sort order.

    In your case, to get the records in descending order to WRITTEN (newest first) put this code at the end of your select statement:

    order by News.Written DESC

    blindman

  10. #10
    Join Date
    Sep 2003
    Posts
    65
    Originally posted by blindman
    When you define an ORDER BY CLAUSE, you can specify whether you want ascending order (ORDER BY YOURFIELD ASC) or descending order (ORDER BY YOURFIELD DESC). Ascending is the default it you do not explicitly specify a sort order.

    In your case, to get the records in descending order to WRITTEN (newest first) put this code at the end of your select statement:

    order by News.Written DESC

    blindman
    Hey, yeah, the problem with that is that the indexes are going the wrong way, ie number 1 is the oldest record. But I solved it, it works fine now.

    Thanks a lot for your help.

    For anyone who may be interested, this is how I did it:

    CREATE PROCEDURE news_display (@startIndex INT, @endIndex INT)
    AS
    DECLARE @totalArticles INT;
    SELECT @totalArticles = (SELECT count(*) from News);
    select *
    from News
    inner join
    (select News.Written, count(*) as NewsIndex
    from News
    inner join News News2 on News.Written >= News2.Written
    group by News.Written) NewsIndexes
    on News.Written = NewsIndexes.Written
    where NewsIndexes.NewsIndex between @totalArticles - @EndIndex +1 and @totalArticles - @StartIndex + 1 AND NewsID != 1
    order by News.Written Desc
    GO
    -Ashleigh

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Looks like a workable solution, but I think you can reverse the order of the index by reversing the comparison operator in the subquery from

    "inner join News News2 on News.Written >= News2.Written"

    to

    "inner join News News2 on News.Written <= News2.Written"

    So you select statement would be this:

    select *
    from News
    inner join
    (select News.Written, count(*) as NewsIndex
    from News
    inner join News News2 on News.Written <= News2.Written
    group by News.Written) NewsIndexes
    on News.Written = NewsIndexes.Written
    where NewsIndexes.NewsIndex between @EndIndex and @StartIndex AND NewsID != 1
    order by News.Written Desc

    blindman

  12. #12
    Join Date
    Sep 2003
    Posts
    65
    Originally posted by blindman
    Looks like a workable solution, but I think you can reverse the order of the index by reversing the comparison operator in the subquery from

    "inner join News News2 on News.Written >= News2.Written"

    to

    "inner join News News2 on News.Written <= News2.Written"

    So you select statement would be this:

    select *
    from News
    inner join
    (select News.Written, count(*) as NewsIndex
    from News
    inner join News News2 on News.Written <= News2.Written
    group by News.Written) NewsIndexes
    on News.Written = NewsIndexes.Written
    where NewsIndexes.NewsIndex between @EndIndex and @StartIndex AND NewsID != 1
    order by News.Written Desc

    blindman
    ah, yeah that'd work too, lol, thx. that's great, I'll change it to that :-) saves the db some processing...

    -Ashleigh
    -Ashleigh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •