0

I am having a hard time understanding subqueries. I am pretty sure that is what I need to accomplish my task, but not sure how to put it together.

I have a unique ID field that auto-increments, so that a new entry always gets a higher number assigned to that ID field.

What I want to do is leave X amount of newer entries out, and order by zipcode (new entries means not retrieved/printed). For example, if there are 64 entries that we have not retrieved to print, we want to retrieve the oldest 60 entries and order them by zipcode.

Something like:
select x,y,z from tbl_address order by zipcode ASC from (select x,y,z from tbl_address where retrieved <> 1 order by ID ASC limit 0,60)

3
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by d5e5
0

... I've been working on it more after this post. Still don't have it, but here is a little more:

These 2 select statements work independently:
SELECT id, firstname, lastname, zipcode FROM requests ORDER BY zipcode ASC
SELECT id FROM requests WHERE retrieved <> 1 ORDER BY ID ASC LIMIT 0,10

I am trying to sort by zipcode the results returned from the second query. I'm trying this but it is not working (no records are selected):
SELECT id, firstname, lastname, zipcode FROM requests ORDER BY zipcode ASC WHERE id = (SELECT id FROM requests WHERE retrieved <> 1 ORDER BY ID ASC LIMIT 0,10

Edited by SolidSolutions: n/a

0

Ok, I think I got it. I still need to test the results to make sure it is in fact pulling what I expect it to....but at least I'm getting results returned and the select statement is making sense.

Here is what I got...if there is something better or you see something wrong, please let me know. Otherwise, maybe this will help someone else who is looking to do a subquery on the same table with MySQL select.

SELECT id, firstname, lastname, zipcode FROM (SELECT * FROM requests WHERE retrieved <> 1 ORDER BY ID ASC LIMIT 0,10) AS zipsort ORDER BY zipcode ASC

This is basically creating a temporary result set. Apparently turning the above into: SELECT id, firstname, lastname, zipcode FROM zipsort ORDER BY zipcode ASC
and zipsort temporary table is equal to the subquery: SELECT * FROM requests WHERE retrieved <> 1 ORDER BY ID ASC LIMIT 0,10

0

Your query is syntactically wrong (WHERE clause after ORDER clause) and semantically wrong because
1. you want to select "WHERE id IN" instead of "WHERE id =" and
2. you do not need to sort the subquery because the sorting is done in the master query.

1

Pretend your subquery in parentheses is just another table. Give it an alias by putting a name after it (in my example I used `a` as an alias.) Your ORDER BY for your main query (which contains the subquery) goes at the end. Something like this:

SELECT x, y, z
FROM (
SELECT *
FROM `tbl_address`
WHERE retrieved <>1
LIMIT 0 , 2
) `a`
ORDER BY z

Edited by d5e5: ORDER BY within subquery not needed

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.