GET VALUE FROM SET RECORDS IN QUERY RESULTS

select my_col1,my_col2,my_col3, @i:=@i+1 as myrow from mytable,(select @i=0)) where @i in (1000,5000,10000,20000)

thanks for the post,

could you explain how it works, i do not understand how it works


thanks

whatever you see starting with a "@" is a user defined variable.
aiklamha gave you the following select

declares a user defined variable called "i", or "@i".

every time he increments i to i+1.
Then he makes a selection as a "where" statement, where the i will have its values either 1000, or 5000, or 10000 or 20000.

Thats what he gives you.

However this SQL statement will not take less time to execute.  It will internally execute completely, and then will scan "@i" looking for the values specified in the "in" identifier.

However, this will work for you as a single statement.
He deserves the full points of the question, I just explained you his syntax

thanks for the clarification, are you saying however it would be just as quick to run 4 seperate queries?

Depends on your data.  It will defenintelly take more than running 1x query, but possibly less than 4x.

Infact it runs it once only, but then it scans the all the records from this query to fetch the desired rows.

I am just saying that if you have 50.000 records, it will fetch all 50.000 in one pass and then scan them to show you only 4 out of 50.000

Also you have to keep in mind that once a query is executed its cashed by MySQL.  So you won't necessarily have 4x the time to re-query the same SQL statement.

However its your go.
To me I would do this programmatically.  You defenintelly have some tools in your programming language to select records in a specific row.

For instance in Delphi, you can get either record count or record number, and ask your dataset to move its pointer to the the recordnumber of your choice.

The fact is that the answer of aiklamha reflects the answer to the SQL statement of your question.

This might look simpler to you but its the same as aiklamaha gave you

// This initializes row to 0 and fetches results
SELECT @row := @row + 1 AS row, t.*
FROM mytable t, (SELECT @row := 0) r

// its the same as to declare it yourself and then select it
SET @row:=0;

SELECT @row := @row + 1 AS row, t.*
FROM mytable t

Select allOpen in new window

Now if you want to use the specific ranges, you can extend my previous solution to this.

I tested it on my own MySQL and works fine

SELECT * FROM(
SELECT @row := @row + 1 AS rownum, t.*
FROM mytable t, (SELECT @row := 0) r) t
WHERE rownum IN (1000, 5000, 10000, 20000);

Select allOpen in new window

or if you like better sence.

Explaining the sql code:

Now since every derived table must have its own alias, you see that I give aliases to
mytable as t
(select @row := 0) as r
and the external table with the "Select * ..." as s

Now why I use external table? its simple.

Its not easy to use a user defined variable inside a WHERE statement.
So the inner select contains the @row as rownum.  So it will appear on the select statement in a column called rownum.

Now with the external "select * ...." I fetch the results of the internal select as simple rows.  So the outside select doesn't know about the "rownum" being a user defined variable and treats it as a simple column.  So since I treat it as a simple column, I can use this column in my "where statement" so I select the specific values of this column.

Mystery Solved

SELECT * FROM
(
  SELECT @row := @row + 1 AS rownum, t.*
  FROM mytable as t, (SELECT @row := 0) as r
) as s
WHERE s.rownum IN (1,5,10);

Select allOpen in new window