CODEWITHSUNDEEP
sqlms-accessrank
Tom Malkin
Tom Malkin

Reputation: 2284

Ordering in Access SQL

I don't know how to order the following data in Access SQL:

Col1     Col2
1        1
1        2
1        3
2        4
2        5
2        6
3        7
3        8
3        9

Where it grabs the lowest value in Col2 where Col1 = 1, and then the lowest value in Col2 where Col1 = 2 etc, leading to sorted data of:

Col1     Col2
1        1
2        4
3        7
1        2
2        5
3        8
1        3
2        6
3        9

Col1 can range from 1 to any number, and Col2 doesn't start from 1, or is consistently incremental (but still in the order of size).

The table also has an auto ID primary key if that helps.

---- Thanks to @shA.t this answer works perfectly. I added a simple table join which works as well:

SELECT t1.Col1, t1.Col2 FROM 
(SELECT Table1.Col1, Table2.Col2 FROM Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID) t1

INNER JOIN 

(SELECT Table1.Col1, Table2.Col2 FROM Table2 INNER JOIN Table1 ON Table2.ID = Table1.ID) t2

ON t1.Col1 = t2.Col1 and t1.Col2 >= t2.Col2
Group by t1.Col1, t1.Col2
ORDER BY Count(t2.Col2), t1.Col1

Upvotes: 0

Views: 65

Answers (1)

shA.t
shA.t

Reputation: 16958

I think you can use a query like this:

SQLFIDDLE DEMO

SELECT t1.Col1, t1.Col2
FROM t t1
    JOIN t t2 ON t1.Col1 = t2.Col1 and t1.Col2 >= t2.Col2
Group by t1.Col1, t1.Col2
ORDER BY Count(t2.Col2), t1.Col1;

Upvotes: 3

Related Questions