I have a query that selects the top 20,000 records from a table based of various criteria.  It  has an order by on a set field.

What i want to do is get the value of the record in position 1000, 5000, 10000 and 20000.  
The 20000 value i have been using the max function to retrive the value, but i cannot work out how to retrieve the other values without having to run 4 seperate queries

is there any way to do this

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad KhanManager, ITCommented:

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

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

Ioannis AnifantakisProgramming InstructorCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

MarcuswAuthor Commented:
thanks for the clarification, are you saying however it would be just as quick to run 4 seperate queries?
Ioannis AnifantakisProgramming InstructorCommented:
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.
Ioannis AnifantakisProgramming InstructorCommented:
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

Open in new window

Ioannis AnifantakisProgramming InstructorCommented:
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 @row := @row + 1 AS rownum, t.*
FROM mytable t, (SELECT @row := 0) r) t
WHERE rownum IN (1000, 5000, 10000, 20000);

Open in new window

Ioannis AnifantakisProgramming InstructorCommented:
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 @row := @row + 1 AS rownum, t.*
  FROM mytable as t, (SELECT @row := 0) as r
) as s
WHERE s.rownum IN (1,5,10);

Open in new window

Muhammad KhanManager, ITCommented:
the query i gave before won't work as it is.. I am sorry.. yesterday i didn't have mysql to test the query .. .today i tested and modified it to your requirements... the query should go like this

select * from (select @i:=@i+1 as rownum,col1,col2 from table1, (select @i:=0) t)table_2 where table_2.rownum in (1000,5000,10000,20000);

Although ioannisa has explained it very well but let me throw in my two cents...

To understand a complex query which has subqueries and nested queries in it.. the best way is to start with the innermost query.. because thats the way query parser parses/executes the in our case the innermost query is

(select @i:=0) -- This query does nothing but initializing a variable @i to 0...this only executes once for its parent query..

Now the query one level up.. i.e. the parent query of the previous one..

(select @i:=@i+1 as rownum,col1,col2 from table1, (select @i:=0) t)

This Increments and returns initialized variable @i. this will execute with the visit of every record in table1... so if there are 100 records in it... the variable @i will be incremented by 1, 100 times. returning the current value or @i all the way from 1 to 100.. the output of the above query will be something like this

| rownum | col1    | col2    |
|      1 | record1 | record1 |
|      2 | record2 | record2 |
|      3 | record3 | record3 |
|      4 | record4 | record4 |
|      5 | record5 | record5 |
|      100 | record100 | record100 |

The uppsermost query will use the above resultset to pick the records of our choice i.e. 1000,5000,10000,20000

here is the uppermost query i.e.

select * from (select @i:=@i+1 as rownum,col1,col2 from table1, (select @i:=0) t)table_2 where table_2.rownum in (1000,5000,10000,20000);

It selects from the above result set only the records which have rownum = 1000, 5000, 10000,20000

Hope this helps..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.