Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Simple Sql Question

    I have a table keyed as follows:

    Firstname char(10)
    Lastname char(20)

    i have thousands of names in the table.

    I want to select 5 names at a time.

    after i select the 5 names sometimes I want to ask for the next 5.

    Its kind of like a paging transaction.

    I thought i would be able to pass in the last returned key values (example

    select *from table where firstname > "last firstname received" and lastname > "last lastname received"

    and ideas?

  2. #2
    Join Date
    Apr 2004
    Posts
    3
    How about adding either a counter column, or a selected column? Mark them as selected as they are chosen. Or do they have the chance of being selected over again?

  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Use a Stored Procedure, and pass in what page number you want, and how many results per page.
    Code:
    CREATE PROCEDURE dbo.spMyStoredProc
    	--If nothing is passed, default to 25 records per page
    	@prmPageSize int = 25,
    	--If nothing is passed, start at page 1
    	@prmCurrentPage int = 1
    AS
    
    DECLARE @iPageSize int
    DECLARE @iCurrentPage int
    DECLARE @iStartRowID int
    
    SET @iPageSize = @prmPageSize
    SET @iCurrentPage = @prmCurrentPage
    SET @iStartRowID = ( @iPageSize * @iCurrentPage) - @iPageSize
    
    SET ROWCOUNT @iPageSize
    
    SELECT field1, field2, field3, (SELECT COUNT(*) FROM tblMyTable) AS RowNumber
    FROM  tblMyTable 
    WHERE
    (SELECT COUNT(*) FROM tblMyTable) > @iStartRowID
    ORDER BY RowNumber ASC
    
    SET ROWCOUNT 0
    GO
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There's nothing wrong with that, but there's nothing wrong with your original idea either, except that you need to make sure the field you pass in to identify the last returned value is indeed a unique key value. If you have two records with the same first and last name (father and son, for instance) one record might not get returned. Make sure you include your primary key in the parameter list.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Right blindman, but names generally don't make for good key fields
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...which is why he should include the Primary Key if he uses this method.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2002
    Posts
    45

    this one's not right

    CREATE PROCEDURE dbo.spMyStoredProc
    --If nothing is passed, default to 25 records per page
    @prmPageSize int = 25,
    --If nothing is passed, start at page 1
    @prmCurrentPage int = 1
    AS

    DECLARE @iPageSize int
    DECLARE @iCurrentPage int
    DECLARE @iStartRowID int

    SET @iPageSize = @prmPageSize
    SET @iCurrentPage = @prmCurrentPage
    SET @iStartRowID = ( @iPageSize * @iCurrentPage) - @iPageSize

    SET ROWCOUNT @iPageSize

    SELECT field1, field2, field3, (SELECT COUNT(*) FROM tblMyTable) AS RowNumber
    FROM tblMyTable
    WHERE
    (SELECT COUNT(*) FROM tblMyTable) > @iStartRowID
    ORDER BY RowNumber ASC

    SET ROWCOUNT 0
    GO


    I plugged it in and get the same results no matter what start page i send in.

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    My bad, forgot the join:
    Code:
    CREATE PROCEDURE dbo.spMyStoredProc
    --If nothing is passed, default to 25 records per page
    @prmPageSize int = 25,
    --If nothing is passed, start at page 1
    @prmCurrentPage int = 1
    AS
    
    DECLARE @iPageSize int
    DECLARE @iCurrentPage int
    DECLARE @iStartRowID int
    
    SET @iPageSize = @prmPageSize
    SET @iCurrentPage = @prmCurrentPage
    SET @iStartRowID = ( @iPageSize * @iCurrentPage) - @iPageSize
    
    SET ROWCOUNT @iPageSize
    
    SELECT field1, field2, field3, (SELECT COUNT(*) FROM tblMyTable t2 WHERE t1.ID > t2.ID) AS RowNumber
    FROM tblMyTable t1
    WHERE
    (SELECT COUNT(*) FROM tblMyTable t2 WHERE t1.ID > t2.ID) > @iStartRowID
    ORDER BY RowNumber ASC
    
    SET ROWCOUNT 0
    GO
    The field "ID" should be the field you are trying to sort by. In your case it'd be the name. So your WHERE clause might be: t1.lastname > t2.lastname AND t1.firstname > t2.firstname (I'm not positive, I can't check this from home at the moment). You may have the same problem that blindman mentioned though, you'd loose some records if they have the exact same name. That's the hazards of using names as keys.
    That which does not kill me postpones the inevitable.

Posting Permissions

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