Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15

    Unanswered: Retreive a particular Record position

    Hi all

    I would like to know how to query for a particular record based on its position like

    Record-pos. - field1-field2
    ---------------------
    1-user1-operator
    2-user2-admin
    3-user3-operator
    4-user4-operator
    5-user5-admin

    Record position is not a field in the database.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Retreive a particular Record position

    Originally posted by venkyt
    Hi all

    I would like to know how to query for a particular record based on its position like

    Record-pos. - field1-field2
    ---------------------
    1-user1-operator
    2-user2-admin
    3-user3-operator
    4-user4-operator
    5-user5-admin

    Record position is not a field in the database.
    I don't understand your problem:

    Do you want a query like

    SELECT field1, field2 from table where field1 = "user" & RecordPosition

    ?
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    Let me explain u my whole problem

    I have a web page where each time the page is visited i have to retreive a record and show the data to the user.

    So, for the first request i have to show the first record
    For second request - second record
    For Nth request > no. of records - show the first record

    So, i'm thinking of a query which will retreive record(N) for each request.
    I can store the value of N in my web page and keep incrementing the value for each request. If N becomes > total no. of records i will reset the value of N to 1 to start retreiving the records.

    Also my table the records will be added or deleted.

    I know that i can retreive all the records and get the Nth record out of it.
    But this is a bit process oriented and so looking for a query to directly retreive Nth record.

  4. #4
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    ok query it is, create this field in your query:

    RowNum: (SELECT Count(*) FROM tblCatalog AS Temp WHERE [temp].[StockNo]<[tblCatalog].[StockNo])+1

    this is a sample that would get you autonumber kind of field in result query....

    basicaly, this is a subquery that creates temp table and counts all previous records....

    you may put you required record number in criteria field and you'll get the record....

    restrictions:

    - must have unique field in a table, in my case [StockNo]
    - if you are going to add some more filters, you must includ it also in subquery

  5. #5
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    Thanx...this works fine for me

    But consider the case with 10 records of StockNo = 1..10
    Delete StockNo = 3 and add later after record = 10.
    so i should be getting the record position of StockNo = 3 as 10 or 11 but this query will be give me the record position as still 3 because the number of records before Stock No = 3 +1 = 4 is still 3.

    Any work around for this will be helpful.

    Originally posted by Strucnjak
    ok query it is, create this field in your query:

    RowNum: (SELECT Count(*) FROM tblCatalog AS Temp WHERE [temp].[StockNo]<[tblCatalog].[StockNo])+1

    this is a sample that would get you autonumber kind of field in result query....

    basicaly, this is a subquery that creates temp table and counts all previous records....

    you may put you required record number in criteria field and you'll get the record....

    restrictions:

    - must have unique field in a table, in my case [StockNo]
    - if you are going to add some more filters, you must includ it also in subquery

  6. #6
    Join Date
    Aug 2003
    Location
    Bosnia & Hercegovina
    Posts
    57
    if you add it as StockNo = 3 it is going to put it in third place (if there are StockNo = 1 and 2)....

    StockNo is merely a key, not a value, so if you add same record again you shouldn't reuse key, but rather create new key. you reuse key only if you want to restore some deleted record in it's previous light....

    now a sample:

    if you use stockno as key, you'll have main info in say, description, UOM and such field.....

    if you delete stockno = 3, that had description 'shovel' and UOM 'each', and now want to add it again, you should get new key, say 11, and add info again....

    and in the end it's just matter of whatever you make your primary key and sorting....

  7. #7
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    But my table should have a max. of 100 records. If the 100 records are filled the new record getting added should overwrit the first record1.

    Anyway i have a workaround. Since the primary_key of my table is not a specific field for the user and is used just to find the record position, i can delete first record with the condition WHERE (min(primary key)) and insert the new record with a max(primary key) + 1 number.

    Thanks once again for the help.

    Originally posted by Strucnjak
    if you add it as StockNo = 3 it is going to put it in third place (if there are StockNo = 1 and 2)....

    StockNo is merely a key, not a value, so if you add same record again you shouldn't reuse key, but rather create new key. you reuse key only if you want to restore some deleted record in it's previous light....

    now a sample:

    if you use stockno as key, you'll have main info in say, description, UOM and such field.....

    if you delete stockno = 3, that had description 'shovel' and UOM 'each', and now want to add it again, you should get new key, say 11, and add info again....

    and in the end it's just matter of whatever you make your primary key and sorting....

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by venkyt
    Thanx...this works fine for me

    But consider the case with 10 records of StockNo = 1..10
    Delete StockNo = 3 and add later after record = 10.
    so i should be getting the record position of StockNo = 3 as 10 or 11 but this query will be give me the record position as still 3 because the number of records before Stock No = 3 +1 = 4 is still 3.

    Any work around for this will be helpful.
    I guess, you should search the solution in selecting the Nth record in a recordset by first selecting the top N records, and selecting the TOP 1 record from beneath according to your ordering in time. You can put that into a stroed procedure:

    CREATE PROCEDURE NthRecord(@Cond as char(5), @Nth as int) AS SELECT TOP 1 * FROM (SELECT TOP 3 * FROM Orders WHERE customerid = @Cond ORDER BY orderid ASC) G ORDER BY orderid DESC

    I only don't know how to insert the N value into my query.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    I'm using access database so no stored procedures.

    Anyway i'm clear of one thing that i can't do it in one single step as i have one more condition to get new record inserted if any. otherwise just get the next record from the old bunch.
    So for each user i need to maintain the state of his current record no and the last new record he retreived. If any new record is inserted i need to update the last new record no. for this user and return the new record. If no new records just send him the current record no + 1 record and update the current record pos for the user.

    tblUser(UserID, CurrentRecNo, NewRecNo)
    tblList(RecordPos (PK-Autonumber), field2, field3, fiel4..)

    So if a new record is inserted in tblList the RecordPos is also updated in tblUser.NewRecNo to let the user that a new record is inserted. User will retreive this record pos and empty it so that the next time he will continue with his previous CurrentRecNo + 1 position.

    So i will play around with my application and database.



    Originally posted by DoktorBlue
    I guess, you should search the solution in selecting the Nth record in a recordset by first selecting the top N records, and selecting the TOP 1 record from beneath according to your ordering in time. You can put that into a stroed procedure:

    CREATE PROCEDURE NthRecord(@Cond as char(5), @Nth as int) AS SELECT TOP 1 * FROM (SELECT TOP 3 * FROM Orders WHERE customerid = @Cond ORDER BY orderid ASC) G ORDER BY orderid DESC

    I only don't know how to insert the N value into my query.

  10. #10
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Make a SELECT query from my code
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  11. #11
    Join Date
    Aug 2003
    Location
    Bangalore
    Posts
    15
    OK...i do that...thanks for the help.


    Originally posted by DoktorBlue
    Make a SELECT query from my code

Posting Permissions

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