CODEWITHSUNDEEP
sqlpostgresqlsortingsubquerysql-order-by
niczak
niczak

Reputation: 3917

Sub queries and sorting? (ORDER BY)

So my syntax is apparently correct in all three cases (PostgreSQL isn't grousing about anything) but the results come back in the same order with all three of these queries. Even stranger when I add/remove DESC from any of the following it has no impact either. Is it possible to sort results based on elements of a sub query 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 am just not sure why the ORDER BY conditions are having no impact on the order of the results.

Upvotes: 2

Views: 22196

Answers (4)

niczak
niczak

Reputation: 3917

What I ended up doing was using the array column in my view (in this case articles_view) to do all my sorting. That way I do all my sorts on a "column" in the primary query and totally avoid having to use JOINS. The way the view is defined, all the columns matching a given pubid (primary key) in the people/status table (both have a 1->many) are stored in array columns in the view. My query with the sort 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 this works is because I always know that the first member of the array (in Postgres the first index is 1 rather than the usual 0), is the primary author (or primary status) which is what I need for sorting.

Upvotes: 0

Simon Fox
Simon Fox

Reputation: 10601

All the sub queries are doing is providing a set of results for the condition to check for the existence of spubid. You need to actually join to the status table and then use the columns in an order by clause on 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

Upvotes: 3

VoteyDisciple
VoteyDisciple

Reputation: 37813

You're not ordering the outer query; you're only ordering the inner query. It's perfectly legal, but all you're doing with those inner results is comparing spubid against them, and it doesn't much matter what order you do that in.

What you're looking for is a 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 could rewrite the the other lookup as a join also, but for simplicity I left that one alone.)

Upvotes: 2

Tom Neyland
Tom Neyland

Reputation: 6968

You are only sorting the data that is used by the IN statements. You need to sort your top level Select statement.

Edit:

And since the Select statements inside of the IN clauses are not contributing to the overall sorting of your results, you should remove the order by clauses from them, thus preventing the server from having to do unneeded processing.

Upvotes: 1

Related Questions