trouble with ORDER BY (1 Viewer)

zakary · Jan 3, 2015

As part of a query I have the following (needs to be Access 2003 sql compatible, I'm sill wearing the learner badge!) ...

Minimal Code:

Code:

 SELECT Main.Ax,Main.Ay,
 (select Astr from Afull where Avalid = Main.Ax) as AxStr,
 (select Astr from Afull where Avalid = Main.Ay) as AyStr,
 Switch(AxStr>=0, AxStr,AxStr<0,0,isnull(AxStr),0)
 + Switch(AyStr>=0, AyStr,AyStr<0,0,isnull(AyStr),0) as AStrTotal
 FROM Main

However, I have a problem when I try to include Order By 5 Asc in that it doesn't work. I'm guessing this has something to do with the 2 previous subqueries (then AStrTotal adds them together).

Table Main (primary database to be searched) comprises 2 fields Ax and Ay...

Ax Ay
1 6
5 9
3 3
7
5 5
7 2
2
4 4
3
6 5
7 6
etc.... Blank entries above are simply null values. Blanks, Ax and Ay values can appear in either field.

Table Afull comprises 2 fields called Avalid and Astr...
Avalid AStr
1
2
3
4
5
6
7
8
9

Field Astr is initialised to Null at the start of each run. The first use of this table is to store all valid values for Ax and Ay in field Avalid. The second use is to allow for the selection, by the user, of search critera. To do this, table Afull is added as a subform in the user search form. The user then selects an Avalid value to search for by inputting any value >0 into Astr - next to the value to be searched. Later, the Astr value (variable number indicating strength) will be used in ORDER BY

An sql query string is then built up whose purpose is to return all records carrying any 'permutation' of user-selected Avalid values - I've not included this restriction in the minimal examples here...I'm trying to get the ORDER BY working first.

I've now reduced the original query to...
Minimal code:

Code:

SELECT Main.Ax,Main.Ay,
(select Astr from Afull where Avalid = Main.Ax) as AxStr
FROM Main

Then adding...

Code:

ORDER BY (select Astr from Afull where Avalid = Main.Ax) ASC;

But I get a syntax error in ORDER BY. Removing the brackets doesn't help. ORDER BY 3 ASC compiles but doesn't work.

Is there any get the order by working properly.

MarkK · Jan 3, 2015

To sort on AxStr I would do this . . .

Code:

SELECT t.* 
FROM 
   (
   SELECT Main.Ax, Main.Ay,
      (
      SELECT Astr 
      FROM Afull 
      WHERE Avalid = Main.Ax
      ) as AxStr
   FROM Main
   ) As t
ORDER BY t.AxStr

zakary · Jan 3, 2015

Many thanks for this, Mark. Greatly appreciated.

I've extended your solution into the original minimal code. This now allows me to sort according to AStrTotal (AxStr + AyStr).
One, initial, interesting characteristic was that sorting using AStrTotal seemed to be text based rather than numeric - e.g. I'd get 1034 appearing before 11. By the use of the Cint statement I was able to convert AStrTotal to numeric, solving the issue...I hope (I understand that CAST would be used in other sql variants). I hope the finished product seems reasonable and watertight.

Code:

SELECT t.*
FROM
    (
    SELECT 
         Main.Ax,
         Main.Ay,
         (SELECT Astr from Afull where Avalid = Main.Ax) as AxStr,
         (SELECT Astr from Afull where Avalid = Main.Ay) as AyStr,
         Cint(Switch(AxStr>=0, AxStr,AxStr<0,0,isnull(AxStr),0)
         + 
         Switch(AyStr>=0, AyStr,AyStr<0,0,isnull(AyStr),0)) as AStrTotal
   FROM Main 
   ) As t
ORDER BY t.AStrTotal ASC;

trouble with ORDER BY (1 Viewer)

zakary

New member

MarkK

bit cruncher

zakary

New member

Similar threads

Users who are viewing this thread