Dan
Dan

Reputation: 3

SQL Sorting of Grouped Data

I'm looking for a way to return data in a very specific order from an Access database using SQL. I want look for the lowest frequency, list all systems in frequency order starting with the system of the lowest frequency, then do the same operation on the next highest frequency and so on.

This is how I would like the output to be. (The line breaks are just for ease of seeing the sort order, I will add those in the program code later.)

System   Freq
------  ------
FM        100
FM        120
FM        150

AM        110
AM        150
AM        170

CW        160
CW        180
CW        200

Is something like this possible with SQL? Any help would be greatly appreciated. Thank you.

Upvotes: 0

Views: 1366

Answers (3)

YetAnotherUser
YetAnotherUser

Reputation: 9346

Is this what you want?

Select System, Freq
from YourTableName
Order By System, Freq

Or if I 're-read' your question correctly probably this is what you want (apologies for a very back of envelop code)

Select  YT1.[System], YT1.[Freq]
From    YourTable YT1
        Inner Join (
            Select [System], Min(Freq) MinFreq
            From    YourTable YT2
            Group By [System]
        ) YT3 on YT1.[System] = YT3.[System]
Order By YT3.[MinFreq], YT1.[System], YT1.Freq

Upvotes: 3

Anthony Accioly
Anthony Accioly

Reputation: 22461

General answer is yes, give an alias for your Freq computed column and order by both System and Freq: See an example for multiple row sorting here.

Upvotes: 0

Tom
Tom

Reputation: 1179

Without a clearer picture of your source data, this looks like it's just a simple order by on multiple columns:

SELECT [System], [Freq]
FROM [TableName]
ORDER BY [Freq], [System]

Upvotes: -1

Related Questions