473,323 Members | 1,574 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,323 software developers and data experts.

ORDER BY a SubSelect?

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
Fish FileB
Green FileA
Nofile1 (NULL)
Nofile2 (NULL)
Nofile3 (NULL)
Square FileC
Trapezoid FileC
Triangle FileC
Yellow FileA

Table2:
DateImported Filename
9/20/2005 FileA
9/21/2005 FileB
9/22/2005 FileC
I'd like to get the full listing of elements from Table1, sorted by the
DateImported field that corresponds to them by the two tables
"Filename" field.

I know that one way of doing it would be like so:
SELECT DateImported, *
FROM TABLE1, TABLE2
WHERE Table2.Filename=Table1.Filename
ORDER BY DateImported;

However, that query does not return the records from Table1 that have a
(NULL) value for their Filename.

My next guess was to do this:

SELECT (SELECT DateImported FROM Table2 WHERE
Table2.Filename=Table1.Filename) AS ImportDate, *
FROM TABLE1
ORDER BY ImportDate;

If you take out the "ORDER BY ImportDate" from this query, it returns
ALL the elements from Table1, just like I want and once I have them I
can sort them by their ImportDate by right-clicking on the column name.
However, I need the query to do the sorting and when I leave the
"ORDER BY ImportDate" on the end of that query, Access for some reason
doesn't realize that it's the name of my subselect column.

Any ideas?

Nov 13 '05 #1
4 7530
You can order by a subquery result value, but you need to duplicate the entire
subquery in the order by clause. You can't refer to the select column name.
In your case, though, an outer join would make much more sense.

SELECT TABLE2.DateImported, TABLE1.*
FROM TABLE1 LEFT JOIN TABLE2
ON TABLE1.Filename=TABLE2.Filename
ORDER BY TABLE2.DateImported;

I recommend always qualifying all the names because there could be duplicated
names between your tables (either now or later), and also because without name
qualifiers, when you go back to maintain the query, you have to remember what
field comes from where or dig through all the table definitions to find out.

On 22 Sep 2005 08:10:52 -0700, dt******@gmail.com wrote:
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
Fish FileB
Green FileA
Nofile1 (NULL)
Nofile2 (NULL)
Nofile3 (NULL)
Square FileC
Trapezoid FileC
Triangle FileC
Yellow FileA

Table2:
DateImported Filename
9/20/2005 FileA
9/21/2005 FileB
9/22/2005 FileC
I'd like to get the full listing of elements from Table1, sorted by the
DateImported field that corresponds to them by the two tables
"Filename" field.

I know that one way of doing it would be like so:
SELECT DateImported, *
FROM TABLE1, TABLE2
WHERE Table2.Filename=Table1.Filename
ORDER BY DateImported;

However, that query does not return the records from Table1 that have a
(NULL) value for their Filename.

My next guess was to do this:

SELECT (SELECT DateImported FROM Table2 WHERE
Table2.Filename=Table1.Filename) AS ImportDate, *
FROM TABLE1
ORDER BY ImportDate;

If you take out the "ORDER BY ImportDate" from this query, it returns
ALL the elements from Table1, just like I want and once I have them I
can sort them by their ImportDate by right-clicking on the column name.
However, I need the query to do the sorting and when I leave the
"ORDER BY ImportDate" on the end of that query, Access for some reason
doesn't realize that it's the name of my subselect column.

Any ideas?


Nov 13 '05 #2
is this what u mean?

SELECT Table2.dateImport, Table2.filename, Table1.testData
FROM Table2 RIGHT JOIN Table1 ON Table2.filename = Table1.filename
ORDER BY Table2.dateImport DESC;

Nov 13 '05 #3
I tried duplicating the entire subquery in the order by clauase and
Access gave me an error. The statement you put together with the outer
join works and gives me exactly what I was looking for. Thank you.

Also, you're right about always qualifying the names, that's much more
clear. Thanks again.

Nov 13 '05 #4
On 22 Sep 2005 09:10:40 -0700, dt******@gmail.com wrote:
I tried duplicating the entire subquery in the order by clauase and
Access gave me an error. The statement you put together with the outer
You probably pasted the " As <fieldname>" part. That part is not valid in the
ORDER BY clause.
join works and gives me exactly what I was looking for. Thank you.

Also, you're right about always qualifying the names, that's much more
clear. Thanks again.


Nov 13 '05 #5

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

Similar topics

0
by: limbert | last post by:
------=_NextPart_000_0001_01C34D7B.DFBF53C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 I was reading the manual and it said that...
6
by: Greg Stark | last post by:
So I have a query in which some of the select values are subqueries. The subqueries are aggregates so I don't want to turn this into a join, it would become too complex and postgres would have...
7
by: Jean-Francois.Doyon | last post by:
Hello, I'm trying to retrieve a limited number of random rows, and order them by a column, and am not having any luck with that last part: SELECT * FROM tablename ORDER BY random(), id LIMIT...
3
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...
4
by: James | last post by:
I have a performance problem with the following query and variations on the subselect. The EXISTS version of the first example will complete in ~10 minutes. The NOT logic in both the examples...
5
by: Bob Stearns | last post by:
Is there an easy way (without duplication of the complex expression) to use the same complex expression in all three places? Will something like this work? WITH (SELECT t.*, <complex expr> AS...
1
by: Marco Lazzeri | last post by:
I'd like to reference values returned by a subselect in the same SELECT query. Example: SELECT id, ( SELECT COUNT(*) FROM second ) AS value_to_reference, ( value_to_reference + 1 ) AS...
2
by: Jerry LeVan | last post by:
I have an srf sql function "annual_report(<year>)" that as 14 columns, a category, 12 month numeric columns, and a total numeric column. The function finds monthly totals for each category...
2
by: Morten K. Poulsen | last post by:
(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....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.