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)

... 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

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

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.

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.