By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,978 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,978 IT Pros & Developers. It's quick & easy.

ORDER BY a SubSelect?

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.