sorting ACCESS sql query
I made another thread which solved my problem, however I got the report reviewed and they want it a revision. I will do my best to explain:
Consider the following:
I just need the ORDER BY part of the sql query. The three fields are ACRNo
, Type
and Pty
.
I want it to sort by Type first.
I then want it to sort by Pty. However, I want the blank ones at the end. and the sorted ones on the top. (a normal
orderby
puts the blank ones on top).After that has been sorted, I want the ACR numbers to be Sorted FOR ALL THE BLANK
PTY
. I dont want the ACR's to 开发者_JAVA百科be sorted (or i dont really care) when they have a pty attached to them. However when the Pty is blank, i want the highest acrnumber on top.
I hope this makes sense.
There's probably a better way but here's a hackish method that should work unless you have Pty's starting with a bunch of z's:
order by Type, Pty + 'zzz',ACRNo desc
ORDER BY
type,
IIF(pty IS NULL, 1, 0),
pty,
acrno
This assumes that by "blank PTY" you mean NULL. If you want actual empty strings to be at the bottom as well then you'll need to change it slightly:
ORDER BY
type,
IIF(NZ(pty, '') = '', 1, 0),
NZ(pty, ''),
acrno
SQL sorts on the columns (or expressions) in the order in which they are listed in the ORDER BY clause. So, the above will sort by "type" first, then for rows with the same value for "type" it will sort by the IIF() statement. In this case, the IIF() returns 1 if Pty has no value, otherwise it will return 0. So, the non-valued Pty rows will be sorted after those with a value. Then it goes on to sort by Pty (where all previous expressions in the ORDER BY have the same value) and ACRNo if they have the same Pty value.
精彩评论