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.
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
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
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