Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    8

    Unanswered: newbie help with a query

    hi

    i wonder if u can give me a hand a query?
    what i'm trying to do is to select the 3rd row of the table after sorting it by user name, but because users come and go on a frequent basis so i cannot rely on it. also can't use other fields to select from as they are not unique or sortable.
    i know this should be a very simple problem, but i cant think of a solution.

    thanx in advance

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select * from yourtable
    order by username
    limit 1 offset 2


    rudy
    http://r937.com/
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Posts
    8

    Re: newbie help with a query

    Thank you very much...

  4. #4
    Join Date
    Oct 2003
    Posts
    8
    sorry to bother u again, but what about if i want to update information in that column?

    say if i want to modify the status field of that user to 'happy', how would i go about that? because i dont know how to use offset and limit in a update command

    thanx
    Last edited by mickkai; 10-27-03 at 08:49.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'd handle that situation most carefully

    you said you "can't use other fields to select from as they are not unique or sortable"

    which suggests that the user name should be unique

    which i would not rely on unless i had defined it as a primary key

    i would only every issue an UPDATE statement if i knew for sure which row i was targetting, based on the row contents, not the position within an ORDER BY, since there might be other rows that meet the same row content criteria

    rudy

  6. #6
    Join Date
    Oct 2003
    Posts
    8
    well, the user name is unique and is defined as a primary key, so if i sort by name, then it would resemble some sort of order without duplicate rows

    maybe it'll help if i explain what i'm trying to do.

    i'm trying to select a user as sort of a 'leader' to this group, like in chat room where u have only one host. but instead of selecting the person who has been there the longest, i just select someone from random within a group. there are several groups in total, but i think i can just use a where clause to solve that part.
    the thing is, i only want one leader of each group at a time, so what i'm doing is to reset the status of everyone in the group i want to update, then change the status of one of them to leader. i tried using offset, where the offset is a random number i've generated elsewhere, stored as a variable. so i really dont mind who the new leader is, as long as there is one, which is why i dont mind using the order by to get new the new row

    any more help would be much appreciated
    Last edited by mickkai; 10-27-03 at 09:13.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, if you are resetting the status of everyone in a group, then an extra query should not make that much of an impact on your processing speed, so...

    pull out the 3rd username with one query, then use it in a subsequent UPDATE query to target the row to update

    i don't have postgresql to test on, so i don't know whether you could do the selection of the 3rd name in a subquery of the UPDATE, but if two steps isn't going to kill your response time, that'd be the simplest

  8. #8
    Join Date
    Oct 2003
    Posts
    8
    i've tried using the query above as the new table, like this:

    update (select * from mydb order by name limit 1 offset 2) set status = leader;

    but it said i've got a parse error or near (

    so i dont know what's wrong, but it doesn't seem to work, wherever it's syntax or whatever

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would expect it to be something like this --

    update mydb
    set status = leader
    where name =
    ( select name
    from mydb
    order by name
    limit 1 offset 2 )

    but like i said, i don't have postgresql and can't test it

    rudy

  10. #10
    Join Date
    Oct 2003
    Posts
    8
    cool, that works, thanx!!!

Posting Permissions

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