MYSQL :: Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause

Using Column Alias Name In WHERE Clause
I am giving alias name to the column in SELECT clause and when I am using same alias name in WHERE cluase, it is giving error.

We can use alias names in WHERE clause.

Is there any solution or other way to achieve same functionality?

QUERY:
select concate(firstname, " ", lastname) as name
from user
where name = "Chetan Parekh";

Column Alias And HAVING Clause
I am having problems with the HAVING clause. I know it can use aliases
but the alias I am using has a space in it, eg 'Device ID'

My querry looks like (simplified, devID is a calculation)

SELECT devID AS 'Device ID' FROM tblDevice HAVING 'Device ID' = '123'

and it does not work

but if I use

SELECT devID AS 'Device_ID' FROM tblDevice HAVING Device_ID = '123'

everything works.

How can I use an alias that contains a space in the HAVING clause?

Column Alias In WHERE Clause
I wana select 2 fields of a table as a variable and then in that query use that variable like this
SELECT money1+money2 AS money WHERE money > 10000
but mysql return an sysntax error Is there any way to use a varible like this in a query?

Subquery With IN Clause
I have a table full of subscriptions to a service. this is our second year, and i want to see how many people returned for a new season. I am having a hard time finding a solution to this problem. so far i have this...

SELECT
count(*)
FROM product
WHERE
DATE_FORMAT(insert_date,"%Y")='2005'
AND
payer_email IN
(SELECT DISTINCT
payer_email
FROM product
WHERE DATE_FORMAT(insert_date,'%Y')='2004')

but the page just never loads when i run that. Does anyone have any ideas as to what's wrong with my query, or a better query i might make?

Correlated Subquery In From Clause - Help!
I have read that correlated subqueries in from clauses are illegal as the inner query is evaluated before the outside query. Anyone know a getaround?

My query is as follows:

SELECT
lodging_master. * ,
lodging_type.lod_type_name_mil,
count(lod_id) AS allocations
FROM lodging_master
INNER JOIN index_page_allocations ON lod_id = index_page_alloc_lod_id
INNER JOIN index_pages ON index_page_alloc_index_page_id = index_page_id
INNER JOIN lodging_links_allocation ON index_page_alloc_lod_id = lod_link_alloc_lod_id
INNER JOIN lodging_links AS t2 ON lod_link_alloc_link_id = link_id
INNER JOIN sources ON link_source_id = source_id
LEFT JOIN lodging_type ON lodging_master.lod_type_id = lodging_type.lod_type_id

LEFT JOIN (

SELECT xml_link_id, xml_result_value
FROM
xml_results
WHERE
xml_room_occupancy IN(1,2) AND
xml_nights = 1 AND
xml_date != &#55612;&#57200;-00-00' AND
xml_question_id != 0 AND
xml_result_value != 'X' AND
xml_link_id = t2.link_id
ORDER BY CAST(xml_result_value AS UNSIGNED)
) as t1 ON t1.xml_link_id = link_id

WHERE
index_page_id = 14278
AND lod_link_alloc_status = 'ok' AND
link_status_mil IN('ok', 'hidden') AND
source_status_mil = 'ok'
AND lod_status_mil = 'ok'
GROUP BY lod_id
ORDER BY lod_id LIMIT 0, 15
I keep getting the error:

#1109 - Unknown table 't2' in where clause

Subquery In The SELECT Clause
I am having the problem in the sql statement

SELECT name,
(SELECT Sum(xxx) FROM myTable GROUP BY fld) as mySum ,
(mySum + 1)/2 as myVal
FROM myTable1

This resulted in an error!!
How can i use the 'mySum' in this query

Can You Left Join A Subquery In The WHERE Clause?
I am having difficulty with a fairly complex query. I want to LEFT JOIN a subquery in the where clause like this GREATLY simplified example:

SELECT t1.field1, t2.field2
FROM table1 t1,
LEFT JOIN
(select field2
FROM table2
WHERE field3=22) as t2
on t1.id = t2.t1_id

Using MySQL 4.1

You Can't Specify Target Table '' For Update In FROM Clause
I have problem deleting rows from table with subquery referring to the same table. I store tree structure in the table (there is ParentID field for each row that specifies primary key of the parent row). I'm trying to delete all children of the given row which doesn't have their own children. I can't do this from the script by iterating through all rows because there could be thousands of such records.

I thought that the following query will do that:

DELETE P FROM `tree` AS P WHERE P.`ParentID` = @parent AND NOT EXISTS (SELECT * FROM `tree` AS C WHERE C.`ParentID` = P.`ID`)

But this doesn't work, throwing error:

You can't specify target table 'tree' for update in FROM clause

How can I make this work?

Check Empty Table In Where Clause?
the following where clause (expurgated) suits my needs right up until the table emailman is empty (which it frequently is) and then it returns an empty set (which is bad;-)
this is actually an AND emailman... but don't I think the rest is relevant.

WHERE emailman.related_id != contacts.id How can I modify this to allow for emailman to be empty? Something like...

AND (emailman.related_id != contacts.id || NOT EXISTS (emailman.id) )

Unknown Table 'xxxx' In Where Clause
I have been looking through a few of the forum posts here and I haven't found a solution to my problem.

There error I am getting is:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]Unknown table 'girls' in where clause

/xxx/xxx.asp, line xxx

[asp]
Dim RSGirls
Dim RSThumbs
Dim GirlSQLStmt
Dim ThumbSQLStmt

GirlSQLStmt = "SELECT Name, Height, Age, Weight, Bust, Waist, Hips, Pictures, Description "
GirlSQLStmt = GirlSQLStmt & "FROM Girls"

ThumbSQLStmt = "SELECT * FROM tourthumbs WHERE tourthumbs.id = girls.id"

Set RSThumbs = MyConn.Execute(ThumbSQLStmt)
Set RSGirls = MyConn.Execute(GirlSQLStmt)
[/asp]

There are two tables in the database, 'girls' and 'tourthumbs'. I have checked the permissions that I have set for MySQL and made sure that I can access them both. Both tables also exist and so does the id field in both tables.

You Can't Specify Target Table 'ubs' For Update In FROM Clause...
UPDATE ubsc SET date = NOW(),xp_date = now() WHERE ubs_id = (SELECT MAX(ubs_id) FROM ubsc WHERE ubsd_id =(SELECT ubsd FROM ubsd WHERE ubsf_id = 1009))

The above query shows me error

You can't specify target table 'ubs' for update in FROM clause...

Using CSV On WHERE Clause...
Been pulling my hair for the last couple of hours on these two

On my form I have a number of checkboxes to choose which books you want to filter the results by. In the next page the forms gives me: $books = âÆ,201,50', the number are the IDs of each book in the database.

After the WHERE, how can I make it so that:
id_fk_inst_course_desc = any of the ids in my $books list, and not have to do
(id_fk_inst_course_desc = 300 OR id_fk_inst_course_desc = 201 OR id_fk_inst_course_desc = 50)
My other question, I have a lookup table for the books (called registry_inst) with the following fields:
id_fk_registry | id_fk_inst_course_desc | date_create | create_by_id

The numbers from $books correspond to the id_fk_inst_course_desc field.

So in my SELECT clause I have:
GROUP_CONCAT(id_fk_inst_course_desc ORDER BY id_fk_inst_course_desc ASC) AS course_taken
Which puts in a list the books a specific user has taken. This returns a column named course_taken that has a list of the books: 100,500,303,etc.

How can I modify it so that instead of returning a list, it returns fields (columns) named after each course number (100 | 500 | 303 | etc.), and a 1 if the user has taken it, or a 0 is the user hasn't taken the course.

Where Clause
how can i select range like student of 23 to 30 age with query given below. please help sice it gives only age on the basis of date of birth

SELECT name, birth, CURDATE(),
-> (YEAR(CURDATE())-YEAR(birth))
-> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
-> AS age
-> FROM student;

WHERE Clause
I have a problem with geting data from an string variable in to a where clause.
I am programming in VC++ and I use some database.

The problem is like this:
m_adodc.SetRecordSource("SELECT * FROM Persons WHERE City='Sandnes' ");

m_adodc=my member var; not important
SetRecordSource(" xxx ")=my method;not important
what I want is to give to the "City" an variable name
It dosen`t work if I write:

m_adodc.SetRecordSource("SELECT * FROM Persons WHERE City='some_string_var' ");

or

m_adodc.SetRecordSource("SELECT * FROM Persons WHERE City=some_string_var");
or anything else.

Where Clause
I am getting no data when I place the WHERE clause in my code. The variable pondID is the problem. When I replace the pondID variable with a constant the query works. If I use the variable pondID that I passed to the function the query does not work. Can someone tell me what I am doing wrong?

MYSQL_RES* CAccessMYSQL::GetHumidity_MinMaxAvg(int pondID)
{
CString strQuery;
strQuery.Format("SELECT pondNum, humidity, timestamp
FROM identification JOIN raw_pondreadings
ON identification.id_record = raw_pondreadings.entry
WHERE pondNum = pondID");

Where Clause...
I've a fixed query like

select * from utente where nome=...;

i can't change the query,but during my application i need sometime to do this query without where clause,that is i need all the utente rows without filtering on name..
Cause i can't eliminate where clause,is there in sql a key word that passed to

where nome=.....

means take all the names?

MIN In HAVING Clause
I'm trying to run a query that will delete the record that has the lowest ID. But what I'm trying doesn't seem to have be working, and is spitting out Error: 1064.

Anyone able to shed some light on the matter? Here's the query:

DELETE
FROM podcasts
WHERE feedURL='$podcast' GROUP BY feedURL HAVING MIN(id).

WHERE Clause
I'm rather new to mySQL so I don't know whats causing the jam up here, I keep getting a

Parse error: parse error, unexpected '=' in

When I add this to part of a query

$sth = $DB->query("SELECT * FROM ".$PREFIX."iptrack WHERE FID = ".$link[0] AND IP = ".ip2long($ip));
$result=mysql_query($DB);

Clause
SELECT 'Attendee Type'=ATTEND_TP 'Total Salary'=SUM(SALARY), 'Average Salary'=AVG(SALARY)
FROM INDIVIDUAL
WHERE SALARY > 0
GROUP BY ATTEND_TP

I want to check if 'Total Salary' >100 how do i do that? Not salary. To be more precise I have column Z=a*b Now I want to check z>100. How do I refer Z in where clause??

Select a, sum(a*b) AS z
from c,d
WHERE
Z>100

This doesn't work :P

Where Clause
I am getting problem while retrieving records from mysql table with where clause query.

SELECT * FROM `products` where `cost`=2.01

does not show any record.
Please help me to find out where is the problem.
why the above query does not get the record ?
My table Structure with one record.
Code:

CREATE TABLE `products` (
`part_num` varchar(15) NOT NULL default '',
`cost` float(10,2) default '0.00',
PRIMARY KEY (`part_num`)
) TYPE=MyISAM;

INSERT INTO `products` VALUES("HP001", "2.01");

SQL Clause
Is there a SQL statement that can accomplish this or is it something I will have to code to handle?

I have a table, events, with a field, eventCategory. I need to pull each unique value of the field eventCategory . Otherwise, if eventCategory has the value "football" and it is repeated in > 1 records, I only need it to return it once.

desired output:
football
mens basketball
womens basketball

undesired output:
football
mens basketball
football
womens basketball

WHERE Clause
Does anyone have a solution to this:
I need an where clause which can pick up a value from within a string which has a '+' as the delimiter, for example:

SELECT first_name, last_name, email, user_name
FROM basic_user
WHERE first_name like '%John+Smith%'
OR last_name like '%John+Smith%'

The value 'John+Smith' comes from search field from another app and cannot be changed. It could be 'John' or 'John+Smith' or 'Sydney+John+Smith'
So i need a way of matching first name and/or last name anywhere in the string supplied.

WHERE Clause
I have a problem where I have a lot of aliases in my sql statement and I am trying to use the WHERE clause on them but it doesn't recognize them! Here is a much-simplified example:

SELECT id AS myvar FROM tbl_ship WHERE myvar=11

without the WHERE clause it works. But with it, it says "Unknown column 'myvar'". I could very easily say WHERE id=11 in this over simplified example. However, in my more complicated statements, with IF statements and other stuff, it would be much more complicated without refering to aliases because they represent a whole line of reasoning.

IN Clause
When I attempt to run certain select statements, I get the following results:

>select resource_id from tx_table where tx_id=5
gives back a value of 14

>select * from resource_table where resource_id in (14)
gives back a value of 24

However,
select * from resource_table where resource_id in (select resource_id from tx_table where tx_id=5)
gives the following error:
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select resource_id from tx_table where tx_id=5)' at line 1

WHERE Clause
I have a script that constructs a WHERE clause dynamically. At it's most simple, this WHERE clause would look for a series of unique ID numbers:
WHERE (myID = 1) OR (myID = 2) OR (myID = 3)
Obviously this is simplified. The actual clause can contain potentially dozens of ID numbers.
My questions is if there is a more optimal way to construct a WHERE clause like this. It seems so wasteful to construct a long, repetitive string like this.
In PHP, I'd use something like in_array to compare a value against all of the IDs in an array. I've searched far and wide for a solution in the MySQL documentation, but was unable to find one.

Where Clause
im trying to add a link to a menu if the users access is set to 5. However, I am having problems with the where syntax that I can't seem to sought out. Heres the code:

Code:

$q="SELECT access FROM users WHERE (username = $login)"; // This line is having errors
$result=mysql_query($q, $connection) or die

The database connection script is included previously. I can't see anything wrong with the WHERE clause and its really bothering me. It keeps returning the error Quote: Unknown column 'Xangelo' in 'where clause' where Xangelo is the value for $login which is a cookie.

WHERE Clause
I'm fairly new to SQL and I wanted to know if there was a way in which you can use wildcards within the IN function.
For example, if I had many different strings I wanted to match, I could use:

Code:

SELECT date FROM table WHERE date LIKE '2004-08-23%' OR date LIKE '2004-08-22%' OR date LIKE......

i know that this example could easily be remedied w/ the BETWEEN condition, but what about non-contiguous data? Is there a more concise way?

WHERE Clause
what is proper syntax for WHERE clause when you want select everything except when id = 2?

Is it like:
SELECT ... WHERE id != 2 or
SELECT ... WHERE id NOT 2 or something else?

I tried to search MySQL dev site, but somehow I it difficult to find anything from there

Where Clause
i'm making a new site ad i'm thinking of the most efficient and expandable table structure i can have (more so expandable, but i dont want it if its extremely inefficient), so i thought of basically a table with about 3 columns to hold the users data, one row for each stat on the user, basically the table would be user id, field, and value, this makes it easy to select all fields and values for the user without having to change the table structure when i add a new field

the problem is how would i preform a search on a table with this structure, i need where value = 'abc' when field = 'a' or value = 'cba' when field = 'b' but that does not work, is there any way i could get this effect?

ON EXISTING Clause
What I'm trying to do is update a table of mine in mySQL. However, some of the values may not exist in the table yet, while most will already be there.

I have done some searching and I think I need help. I'm assuming UPDATE won't simply act as an INSERT if the existing value is not found. From my searching it seems I need to use the ON EXISTING clause with an INSERT. However, it seems that ON EXISTING can only be used when there is a primary key. In my case, thats not the case here.

However, for me this is query confusing at the moment. Can someone provide a hand?

My table is called "rankings" and has the following structure:
comp_id - int (and an index)
uid - int
networth - int

Wildcards In WHERE Clause
I want to import 15.000+ images into the shop system I'm currently working on. The customer told me, that the filenames correspond to the old product numbers. I created a new table for image metadata where the filenames were stored without the file extension. So, to get the relationship I could simply do something like this:

SELECT
products.id
FROM
products, images
WHERE
products.product_number = images.file_name
Unfortunately some files have names like 1234_new or 1234_1 and so on. That's the product number followed by a stupid suffix.

How can I use wildcards or something else in the WHERE clause to get the product? Basically it should do something like this:

WHERE
products.product_number LIKE %images.file_name%

SQL WHERE Clause Question
Is this allowed in the where clause for SQL code?

usergroupid IN (5,6,7) AND userid IN (3)
I'm using the following code, but the results return empty. If I delete the AND userid IN (3) the results return true, same thing if I just delete usergroupid IN (5,6,7) AND

PHP

$administrators = $db->query_read("
        SELECT *
        FROM " . TABLE_PREFIX . "user
        WHERE usergroupid IN (5,6,7) AND userid IN (3)
");

Apparently this is an invalid SQL query, but returns no errors. Just empty results. Any ideas what I'm missing or not doiing correctly? I tried searching on this topic but the text WHERE is filtered out of the search string.

EDIT
I have 3 users in the usertable.

usera (userid 1 - usergroupid - 6) Admin
userb (userid 2 - usergroupid - 7) Moderator
userc (userid 3 - usergroupid - 2) Regular User

The above query should return all users if I'm not mistaking, but it's not returning anything.

Edit: Okay I just relized it won't return anything because it's looking for a userid that is 3 and a usergroupid that is 5,6,or 7. Obvliously no users in the database have those exact matchs. Hmmm... I need to figure out how to display all usergroupids X,Y,Z, and add userid A,B,C even if there not apart of the usergroup X,Y,Z.

How Do I Code This In The WHERE Clause?
I have a form where people can click on checkboxes for their ethnicity. When they click on search it will take all those ethnicity id's and compare it to others in a database and pull out all members that have the same ethnicity.

In the database when a user has put in their own ethnicity it puts all the ethnicity id's in one field (seperated by commas). So how do I code it so that I can take an array of ids and compare it to a field of ids in the databse?

Using WHERE Clause With LIMIT
Can I do something like this in MySQL:

Alternative To NOT IN Clause ?
I am developing a community website which has so many articles that can be seen by people. Here one can track articles by its freshness and whether it has been seen by 'friends' and so on.. If the article is new, and has been commented by friends but not by the logged in user, I bring that article to the user's focus....

The tables concerned are articles, users, comments and friends.

The query is like ....

Masse OR In Where Clause
ID| User_id | event_id | comment |

I have a list of user_ids for example (1,5,155,1999,25,12)

I want every row where user_id is any number in the list..

Select * from user_updates where user_id = 1 or user_id 5 or user_id = 155 or user_id = 1999 or user_id = 25 or user_id = 12;

I belive that the sql above actually would work. But the list of user_id's can be massive. For example 300 different user_id's..

Does anyone have a solution on solving this in any other way?

I belive that a query like that would take up massive memory?

COUNTs In WHERE Clause
I want to select users who occur more than 5 times in a table. I can't think of any other way than this, but I get an error:

SELECT userID,COUNT(userID) AS repeats FROM bandratings WHERE repeats>5 GROUP BY userID

Count In Where Clause?
I have two tables, a person table, and say a queue table. For simplicities sake, the person consists of nothing but their ID.

The queue table consists of a person ID, an itemID, a status, and an entryDate.

The queue can only have two possible statuses, QUEUED, and PROCESSED

Here's what I want:

Given an itemID, I want to find the first person who selected said item that has it in a status of QUEUED, but, that person can't have any more than 1 item in their queue with PROCESSED status.

I tried doing some grouping, and trying to do count(ID) as count, then adding in WHERE count < 2...but doesn't know what 'count' is. If I can do this in one query, it would be wonderful.

IF EXISTS Clause?
Why does "DROP INDEX IF EXISTS" not work?

when "DROP PROCEDURE IF EXISTS" and "DROP TABLE IF EXISTS" etc. does work??

Here is my code

alter table test drop index if exists `index_test`;

It gives me errors. Am I doing somethign wrong?

Use The '=' When Using A Where Clause In MySQL.
I am on the following version of MySQL:

mysql Ver 14.7 Distrib 4.1.13, for suse-linux (i686) using readline 5.0

When I try to use a select or update statement using an '=' the statement does not work. For example, if I do the following:

select epi from epitable where epi = 'user';

the statement never returns anything even though 'user' is in the table. I have tried recreating the table,deleting the table, adding permissions to the column, recreating the data on the table, adding permissions to the table,etc. The table has 3 columns, id, epi,count.

id is a primary key, integer.
epi is a mediumtext
and count is an integer.

Any suggestions?

COUNT In WHERE Clause
I am trying to query a set of tables and it needs to use COUNT(column_name) in where clause. The query looks something like :

doesn't work
select name, count(id) from table1 where count(id) > 2 group by name ;

doesn't work
select name, count(id) as ctid from table1 where ctid > 2 group by name ;

I need only the rows where name has a count of id greater than 2.

LIMIT Clause
I want to be able to use LIMIT to control how many rows from one table are
returned, independent of how many rows there are in a second table that is
joined to the first. Code:

Scope Of HAVING Clause
From the doc:

> Before MySQL 5.0.2, a HAVING clause can refer to any column or alias named in a select_expr in the

> SELECT list or in outer subqueries, and to aggregate functions.
http://dev.mysql.com/doc/mysql/en/select.html

According to the documentation, the following queries should work in MySQL 4.1.11 but it doesnâ€™t.

SELECT COUNT(col1) FROM t GROUP BY col1 HAVING col1 = 2
SELECT SUM(col1) FROM t GROUP BY col1 HAVING col1 = 2

To get it working, you have to specifically add the col1 in the SELECT list as in:

SELECT COUNT(col1), col1 FROM t GROUP BY col1 HAVING col1 = 2

Not a big deal really but what about the doc then?

Subqueries In The FROM Clause
I'm using 1&1 web hosting services with MySQL as database (Ver 14.12 Distrib 5.0.16, for pc-linux-gnu (i686) using readline 5.0).

When I ran an example in Chapter 13.2.8.8 from MySQL 5.0 Reference Manual:

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;

I always get the following error message:

ERROR 1064 (00000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb

More Than One Condition In A HAVING Clause
I have tried to run a query where the reuslts are restricted by a
HAVING clause with 2 conditions, but the second condition seems to be
ignored.

for example

SELECT userid, AVG(position) FROM `table1` WHERE (status='finished')
GROUP BY userid HAVING (COUNT(*) >= 4 AND AVG(position) < 5.5)

returns exactly the same as

SELECT userid, AVG(position) FROM `table1` WHERE (status='finished')
GROUP BY userid HAVING (COUNT(*) >= 4)

even though there are values for AVG(position) returned that are > 6.

Is it the case that I can't have more than one condition in a HAVING
clause? have I got the syntax wrong? Is the behavior different in a
later version?

I am using "mysql Ver 11.15 Distrib 3.23.41, for redhat-linux-gnu
(i386)"

OFFSET Clause?
I want to be able to specify only an offset without a limit I currently use this and it works good but I'm wondering if there is an OFFSET clause or something to do it mysql way

"SELECT * FROM 'yazilar' WHERE tur = 'estetik' LIMIT 5, 999999999999999"

MAX() (or Other Trick) In WHERE Clause
I am trying to execute a query that looks like this:

SELECT MAX(days.created_at) as d,
(SELECT value FROM readings AS r0
WHERE r0.created_at <= MAX(days.created_at)
ORDER BY r0.created_at desc LIMIT 1) as foo
FROM readings AS days
WHERE days.meter_id = 1
GROUP BY YEAR(days.created_at),
MONTH(days.created_at),
DAYOFMONTH(days.created_at)

'value' is a float and 'created_at' is a datetime. This gives me an error message: ERROR 1111 (HY000): Invalid use of group function. I'm guessing that I can't use a MAx() call in a WHERE clause? What should I do instead. I am trying to select grouping on days from 'readings'. Within each group I want to find row with the largest 'created_at ' value, from that row I want to use 'value'. Is there some standar trick that acomplishes this in one query?

LIMIT Clause
I need to retrived a specific row from a query, these these specific
row number is the result of another query.

Is it possible to do that in one query with a subquery (mysql 4.1.x)?
Here is how I try (that gave me a syntax error.):

SELECT a FROM t1 WHERE a = 1 ORDER BY LIMIT (SELECT b FROM t2 WHERE c
= 2),1;

############
the 2 query standalone worked.

SELECT b FROM t2 WHERE c = 2; # return 3

So:
SELECT a FROM t1 WHERE a = 1 ORDER BY LIMIT 3,1; # return the good row