473,837 Members | 1,627 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

subselect, order by and left join

(re-post)

Dear list,

Please let me know if this is not the list to ask this kind of question.

I am trying to optimize a query that joins two relatively large (750000 rows in
each) tables. If I do it using a subselect, I can "force" the planner to choose
the fastest path. Now, my question is:

If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
other table, is the order maintained? Or is PostgreSQL free to return the rows
in any order, after the join?

My query is the following:

SELECT a.*
FROM (SELECT * FROM tree WHERE parent_id=13634 05 ORDER BY order_index DESC) AS a
LEFT JOIN content AS b ON a.object_id=b.i d
WHERE (b.onair = 't') LIMIT 1;

Thanks,
Morten

--
Morten K. Poulsen <mo************ ***@afdelingp.d k>
http://www.afdelingp.dk/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 23 '05 #1
2 7365
On Mon, 8 Nov 2004, Morten K. Poulsen wrote:
Please let me know if this is not the list to ask this kind of question.

I am trying to optimize a query that joins two relatively large (750000 rows in
each) tables. If I do it using a subselect, I can "force" the planner to choose
the fastest path. Now, my question is:

If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
other table, is the order maintained? Or is PostgreSQL free to return the rows
in any order, after the join?


AFAIK, you have no guarantees as to the output order unless you have
another order by. The join may destroy the ordering, so even if you get
the ordering you want right now, you shouldn't rely on it.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
On Mon, Nov 08, 2004 at 04:54:40AM -0800, Stephan Szabo wrote:
If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
other table, is the order maintained? Or is PostgreSQL free to return the
rows in any order, after the join?


AFAIK, you have no guarantees as to the output order unless you have another
order by. The join may destroy the ordering, so even if you get the ordering
you want right now, you shouldn't rely on it.


OK. Thanks for the reply.

Morten

--
Morten K. Poulsen <mo************ ***@afdelingp.d k>
http://www.afdelingp.dk/

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1460
by: Daniel Rossi | last post by:
hi there, i am trying to remove values from a list menu if the join table d= oesnt have keys when a key is selected for instance: locations locationID locations_join locationID shotlistID SELECT SQL_NO_CACHE l.locationID , l.location FROM locations l LEFT JOIN lo= cations_join lj ON l.locationID =3D lj.locationID WHERE l.locationID NOT IN=
3
2226
by: ColdCanuck | last post by:
Help! I'm trying to understand the new ANSI join syntax (after many years of coding using the old style). I am now working with an application that only understands ANSI syntax so I am struggling. My first (old style syntax) SQL statement below produces 60 rows: SELECT A1.CONTACTID, A1.LASTNAME, A1.FIRSTNAME, A1.ACCOUNT, A6.CITY, A6.STATE, A1.WORKPHONE, A1.FAX, A1.EMAIL
2
3047
by: kjc | last post by:
Not sure if this is the right group to post this to but. This is the current query that I have. select tableA.id,tableB.artist,tableB.image,from tableA,tableB where tableA.image = tableB.image AND tableB.price >0 AND tableB.price < 20 order by tableB.price DESC' What I need is, for each row returned I need information from a third and fourth table. tableC, and tableD.
3
7203
by: Neil Zanella | last post by:
Hello, I would like to ask the about the following... PostgreSQL allows tables resulting from subselects to be renamed with an optional AS keyword whereas Oracle 9 will report an error whenever a table is renamed with the AS keyword. Furthermore, in PostgreSQL when the result of a subselect is referenced in an outer select it is required that the subselect result be named, whereas this is not true in Oracle. I wonder what standard SQL...
4
7567
by: dtwalter | last post by:
Is it possible to ORDER BY a SubSelect? I don't see why it wouldn't be, but I'm having some trouble. Hopefully it's just a simple error in syntax and somebody can tell me. Here's what I'm trying to do... I've got two tables: Table1: TestData Filename Bird FileB Blue FileA Circle FileC
6
5018
by: Sebastien | last post by:
I have the following statement which I run successfully in... 1 hour 10 minutes. SELECT a.tsgicd as ACCT_ID, a.tsa5cd as SEC_ID, CASE WHEN (SUBSTRING(a.tsgicd, 6, 1) = 'R' or SUBSTRING(a.tsgicd, 6, 1) = 'Y' or SUBSTRING(a.tsgicd, 6, 1) = '0'
4
23956
by: johnfaulkner | last post by:
Hi, I am trying to perform a single select of data from 2 tables, table A and table B. Table B may have none, one or many corresponding rows. If table B has no corresponding rows then table B values should be set to null. If table B has one or more corresponding rows then table B values should be set to the corresponding table B row with the lowest DATE. I think I should do a left outer join on table A and table B but can't work out...
2
1337
by: speavey | last post by:
When I run this query, I get an ORDER BY error "Incorrect syntax near the keyword 'ORDER'. I've bolded it below. If I take the ORDER by out then it works correctly, but I need the ORDER BY because of the TOP 9. Any Suggestions??? (SELECT TOP 9 trans.vchTrustee, trans.vchCaseNumber, Trans.TotalReceipts + SUM(a.mnyBalanceAmount) As Top9Total FROM tblCase c1 LEFT JOIN tblAsset a ON c1.intCaseID = a.intCaseID LEFT JOIN
1
4455
by: nemesisdan | last post by:
This may seem simple to most, but for some reason i cannot get this to work!! I have 2 tables, table_a & table_b Both tables have a string reference number used to join. table_a will only ever have one occurance of the reference number, where as table_b has multiple records with the same reference reference number, ordered by a column called 'order'. I am trying to pull out all records in table_a and the corresponding record in table_b...
0
9851
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10583
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10639
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9419
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7012
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5863
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4481
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4059
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3128
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.