|
|
Posted by Hilarion on 09/27/05 15:45
>> I need to get the position [...].
>
> In MySQL you could use LIMIT clause like this: [...]
Sorry. I misunderstood you. My suggestion works if you
want to get a record which is on the specified position,
not if you want to get the position of a record.
What Stefan suggested will work if your RDBMS supports
subqueries and name "someone" is distinct in your
table and dates are also distinct.
If you have more than one "someone", than the subquery
would return more than one date and the whole query
will fail (in most RDBMS engines) or will work unpredictably.
If the value in "date" column for "someone" is also
present in some other records, then theres no way to
determine in which order they should go, so the positioning
is unreliable.
In general the problem is very close to the problem of
selecting previous or next record (when we have identified
some "current" record first). Take a look at that discussion
and use the solutions given for retrieving "previous"
without LIMIT and ORDER BY clause, and with column list
replaced with COUNT(*).
The link to the discussion is:
http://groups.google.com/group/alt.php.sql/browse_frm/thread/f86dacb1b139dfee/3f053fb2a6da0bd6?tvc=1&q=3kevu7Fu1vm2U1#3f053fb2a6da0bd6
One of the solutions given (for "previous") was:
SELECT *
FROM some_tables
WHERE (col1 < $curr_col1)
OR (col1 = $curr_col1 AND col2 < $curr_col2)
OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 < $curr_col3)
ORDER BY col1 DESC, col2 DESC, col3 DESC
LIMIT 1
It assumed that the records are ordered by: col1 ASC, col2 ASC, col3 ASC
and that you have values of those columns for the "current" record
in the $curr_col1, $curr_col2 and $curr_col3 variables. To make
it work for you (to get the position of the "current" record), you should
change that query to:
SELECT COUNT(*)+1
FROM some_tables
WHERE (col1 < $curr_col1)
OR (col1 = $curr_col1 AND col2 < $curr_col2)
OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 < $curr_col3)
or:
SELECT COUNT(*)
FROM some_tables
WHERE (col1 < $curr_col1)
OR (col1 = $curr_col1 AND col2 < $curr_col2)
OR (col1 = $curr_col1 AND col2 = $curr_col2 AND col3 <= $curr_col3)
Be sure to read about this solution limitations which in general are:
- the list of columns used to order the records should give UNIQUE
sets of values for all records (so if you have two or more records
with same values for col1, col2 and col3, then you should add
some columns which make the records distinct to the ORDER BY,
or - in this case - to the WHERE clause).
If you would like to calculate the position of current record
when the values are not distinct and should not be made distinct,
and the position calculations should see not distinct records as
one, than you'll probably have to use subquery.
Example for such situation is when you give scores for some players
and two or more players can have same score and in this case
should be considered to have same position in the score tables.
In this case if you want to get the position of a player with
score equal to (for example) 550, then you should use this query:
SELECT COUNT(*)
FROM (
SELECT DISTINCT score
FROM players
WHERE score <= 550
)
Hilarion
Navigation:
[Reply to this message]
|