Sub-queries and sorting? (SORT BY)
So my syntax seems to be correct in all three cases (PostgreSQL doesn't loop on anything), but the results are returned in the same order with all three of these queries. Even a stranger, when I add / remove DESC from any of the following, it has no effect either. Is it possible to sort the results based on the elements of the subquery or not?
Sort by affiliation
SELECT * FROM articles_view WHERE (1=1)
AND spubid IN
(SELECT people.spubid FROM people WHERE (people.slast ilike 'doe')
GROUP BY people.spubid, people.slast, people.saffil)
AND spubid IN
(SELECT status.spubid FROM status WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008) ORDER BY status.iyear, status.imonth)
Sort by last name, descending order
SELECT * FROM articles_view WHERE (1=1)
AND spubid IN
(SELECT people.spubid FROM people WHERE (people.slast ilike 'doe')
GROUP BY people.spubid, people.slast, people.saffil ORDER BY people.slast DESC)
AND spubid IN
(SELECT status.spubid FROM status WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008))
Sort by year/month descending order
SELECT * FROM articles_view WHERE (1=1)
AND spubid IN
(SELECT people.spubid FROM people WHERE (people.slast ilike 'doe')
GROUP BY people.spubid, people.slast, people.saffil )
AND spubid IN
(SELECT status.spubid FROM status WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008) ORDER BY status.iyear, status.imonth DESC)
I just don't know why ORDER BY clauses don't affect the order of results.
********* UPDATE:
As a result, I used an array column in my view (article_view in this case) to do all my sorting. This way I do all of its kind on a "column" in the main query and avoid using JOINS entirely. The way the view is defined, all columns matching a given slogan (primary key) in the people / status table (both have 1-> many) are stored in array columns in the view. My sorted query looks like this:
SELECT * FROM articles_view WHERE
((articles_view.skeywords_auto ilike '%ice%') OR (articles_view.skeywords_manual ilike '%ice%'))
ORDER BY (articles_view.authors[1]).slast
The reason for this is because I always know that the first member of the array (in Postgres, the first index is 1, not the usual 0) is the main author (or main status), which is what I need to sort.
source to share
As a result, I used an array column in my view (article_view in this case) to do all my sorting. This way I do all of its kind on a "column" in the main query and avoid using JOINS entirely. The way the view is defined, all columns matching a given slogan (primary key) in the people / status table (both have 1-> many) are stored in array columns in the view. My sorted query looks like this:
SELECT * FROM articles_view WHERE
((articles_view.skeywords_auto ilike '%ice%') OR (articles_view.skeywords_manual ilike '%ice%'))
ORDER BY (articles_view.authors[1]).slast
The reason for this is because I always know that the first member of the array (in Postgres, the first index is 1, not the usual 0) is the main author (or main status), which is what I need to sort.
source to share
All ancillary queries do this to provide a result set for the condition to test for the existence of spubid. You need to actually join the state table and then use the columns in the order by clause in the outer query.
Something like:
SELECT *
FROM articles_view
INNER JOIN status ON articles_view.spubid = status.spubid
INNER JOIN people ON articles_view.spubid = people.spubid
WHERE ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000)
AND ((status.imonth <= 01 OR status.imonth IS NULL)
AND status.iyear <= 2008 AND people.slast ilike 'doe')
ORDER BY status.iyear, status.imonth
source to share
You are not ordering an external request; you are ordering an internal request. This is perfectly legal, but whatever you do with these internal results compares spubid
to them, and it doesn't really matter in what order you do it.
What you are looking for is this JOIN
.
SELECT *
FROM articles_view
INNER JOIN status ON (status.spubid = articles_view.spubid AND ((status.imonth >= 01 OR status.imonth IS NULL) AND status.iyear >= 2000) AND ((status.imonth <= 01 OR status.imonth IS NULL) AND status.iyear <= 2008))
WHERE spubid IN
(SELECT people.spubid FROM people WHERE (people.slast ilike 'doe')
GROUP BY people.spubid, people.slast, people.saffil )
ORDER BY status.iyear, status.imonth DESC
(You can rewrite another search as a join, but for simplicity I left it alone.)
source to share
You are only sorting data used by IN statements. You need to sort the top level select statement.
Edit:
And since the Select statements within IN clauses do not contribute to the overall sorting of your results, you must remove the ordering from them because of them, thereby preventing the server from doing unnecessary processing.
source to share