Ms-Access -> Select as + Order by = Error

MS-Access - SELECT AS + ORDER BY = error

Aliases are only usable in the query output. You can't use them in other parts of the query. Unfortunately, you'll have to copy and paste the entire subquery to make it work.

Syntax error in Order by clause - SQL in Microsoft Access

You need TOP clause :

SELECT t.*
FROM (SELECT TOP 10 t.*
      FROM `5182` AS t
      ORDER BY t.ID DESC
     ) AS t
ORDER BY t.ID ASC;

MS Access SQL: select rows with the same order as IN clause

It sounds like you need a JOIN...

This should work, although it may need to be translated to Access syntax (which is apparently subtly different):

SELECT b.name, a.title
FROM book as a
JOIN user as b
ON b.id = a.userId
WHERE SUBSTRING(LOWER(a.title), 1, 1) = 'a'
ORDER by a.title

I don't know why you're switching to Access, although I have heard it's been improving in recent years. I think I'd prefer almost any other RDBMS, though. And your schema could probably stand some tweaking, from the sound of things.

MS Access: Error from WHERE condition evaluated on query inside join

First, I would like to thank “Damien_The_Unbeliever”, who guided me, via his comments on my question, to understanding the source of the problem. As usual, once you understand the problem, the solution is at hand. I don’t know why he posted his remarks as comments instead of as an answer. Had he posted them as an answer, I’d have accepted it, but since he didn't I’m posting my own answer here so people can know the solution to the problem. I see that he already has a reputation of over 100k, so presumably he’s not concerned about the answer points.

Summarizing Damien’s comments, the problem was that Access was incorrectly and amazingly mixing the query's WHERE predicate with the join condition in the ON clause of the subquery! This caused it to attempt to evaluate DateVal on records for which that field is not defined, and thereby crash the query.

Here are references on this problem, the first provided by Damien, the others found with some research once I knew what to look for:

• Discussion of a similar problem on Microsoft Connect: SQL Server should not raise illogical errors
• Outer join analog of this problem documented by Allen Browne: Bug: Outer join expressions retrieved wrongly
• Microsoft documentation of a problem that might be related (I haven't checked to see if it's really the same problem): ACC2000: Outer Join with WHERE Clause Returns Unexpected Records
• StackOverflow discussion of the outer join analog of the problem: Access 2007 - Left Join to a query returns #Error instead of Null

In the fourth reference, the answer by “CWeb” gave me the clue to how to develop a workaround. What I had to do was put the WHERE predicate inside an iif() that tests the join condition again, which prevents DateVal from being evaluated on the wrong records. It’s a bit clugey, as workarounds always are, but it works. When I do that, I no longer need the subquery QueryAJoinB, whose purpose was to get the join to happen before the WHERE is evaluated, which wasn’t happening (which was the problem). Here is the query that works now:

SELECT ID_Entry, DateVal
FROM [Query A] INNER JOIN [Query B] ON [Query A].ID_Entry = [Query B].ID_Entry
WHERE IIf([Query A].[ID_Entry] = [Query B].[ID_Entry], [DateVal] = Date(), False)
ORDER BY ID_Entry, DateVal;

Success is sweet! Thanks again, Damien.

MS-Access Select 1 row from GROUP BY query

Here is a solution that scales well (6s on 250k recs in t2) and does what I am asking for.

I could not get Gordon's answer to work in Access. Seems like it should have however. And I have my doubts about how well it would perform with 250k recs in t2. I would love to test a solution like Gordon's, if I could figure out how to get Access to take it.

See problem description for an example on exactly which record I am after. I only need t2.id from the result set. This was not stated originally, but I don't see how that changes the problem statement or solution. I could be wrong there. I still need t3.name, but it can be retrieved later using t2.id.

But I still need to pick the record GROUP'd BY t1.g1, t1.g2 that comes first when all records are sorted by t2.dateandtime, t2.id. Or stated another way, amongst all records with the same t1.g1+t1.g2, I need exactly the first record when the group is sorted by "t2.dateandtime, t2.id".

Perhaps I am thinking about this solution to my problem all wrong, and there are better ways to resolve this with SQL; if so, I would love to hear it.

I seem to have learned that GROUP BY does group records together based on this SQL clause, but this grouping loses any concept of individual records at this point; e.g. you can only extract other fields by using an Aggregate Function (MIN, MAX, SUM, etc), but - and importantly - FIRST does not get the value of the record that you can predict, as the ORDER BY clause has not been performed yet.

With all that said, here is my solution that works.

1. I removed reference to the Join on t3 as with t2.id I can retrieve all the other info I need from t3 after the fact, using t2.id.
2. Don't need to select 't1.g1, t1.g2', that is superfluous. I originally thought that any Group By fields had to be specified in the Select clause also.
3. I combine t2.dateandtime and t2.id into a Text field and use MIN to Select the data/record I am after once it is GROUP'd BY. No need to Sort my result set, as the record with the MIN value of t2.dateandtime, then t2.id has been chosen! Thus satisfying my condition and selection of the correct record.
4. Since all I need is t2.id returned for further processing, I extract t2.id from the String built in #3 and convert back to Long data type.

Here is the brief and simple query:

Select 
 MIN(Format(t2.dateandtime, "yyyymmddhhmmss") & '_' & Format(t2.id, '000000')) as dt_id,  
 CLNG(MID(dt_id, INSTR(dt_id, '_') + 1)) as id
From 
 (table1 t1 Inner Join table2 t2 on t1.fld1=t2.fld1)
Group By
 t1.g1, t1.g2

SQL Query - Using Order By in UNION

Sometimes you need to have the ORDER BY in each of the sections that need to be combined with UNION.

In this case

SELECT * FROM 
(
  SELECT table1.field1 FROM table1 ORDER BY table1.field1
) DUMMY_ALIAS1

UNION ALL

SELECT * FROM
( 
  SELECT table2.field1 FROM table2 ORDER BY table2.field1
) DUMMY_ALIAS2

Simple access query with group by and order by

Try using the latest log_date:

select id_number 
from students_log 
group by id_number 
order by max(log_date)

---

Related Topics