0

I am searching a large database (eg: phone book), and select everyone with the last name "SMITH". That returns (for example) 1000 records.

Next, I want to know the position WITHIN this result set where Mr. Smith with phone number "1234567" exists. (for example, position 300 in the result set)

Is this possible?

TSG
  • 4,242
  • 9
  • 61
  • 121
  • You have to search for it in your result set. – Nelson Apr 15 '14 at 23:20
  • Do you mean iterate record-by-record to find the matching record? (I was hoping to SELECT within the result) – TSG Apr 15 '14 at 23:21
  • Why wouldn't you just do something like: SELECT * FROM phonebook WHERE last_name = "SMITH" and phone_number = "1234567"; Oh, nevermind...your question is about how to get the position of the record in the 1000 records. – dcarrith Apr 15 '14 at 23:24
  • Need more context. It's possible with a Limit statement, but usually it's a really bad idea. – Tony Hopkinson Apr 15 '14 at 23:25
  • As illustrated by wils484 answer the position is only relevant to the query you are searching in and without an explicit order by it's even more problematic. Execute the function again, the position could change. A new index, a different collation, a change to the schema and it could be a completely different answer without adding a record before it. Be very wary, a table is not a rectangular array of cells. – Tony Hopkinson Apr 15 '14 at 23:38

1 Answers1

1

From : With MySQL, how can I generate a column containing the record index in a table?

SELECT t.position_number FROM 
    (SELECT phone_number, @cur_row := @cur_row + 1 AS position_number FROM table
    WHERE last_name = "SMITH") t 
    JOIN ( SELECT @cur_row := 0 ) r 
    WHERE t.phone_number = 123;
Community
  • 1
  • 1
wils484
  • 275
  • 1
  • 3
  • 14
  • 2
    and therein lies the rub, what order are the results in and what results are there. Position in the result set is only relevant to the resultset in the position is in... – Tony Hopkinson Apr 15 '14 at 23:28