CODEWITHSUNDEEP
databasesortingms-access
TechEng
TechEng

Reputation: 45

Specify at what value sorting begins in a query...Access 2016

Is it possible to query a table and return ALL records (no filter) in a specific order and specify the value the sorting should begin at? I want the below query to return all records and sort them by STATE_CODE in ascending order beginning the sort order with STATE_CODE>1. I am not sure if it possible to specify where to start sorting but I want all records returned, not filtered so WHERE will not work here...how is this done?

    SELECT M_ZIPCOD.ZIP, M_ZIPCOD.CITY, M_ZIPCOD.COUNTY, M_ZIPCOD.STATE, 
    M_ZIPCOD.STATE_CODE FROM M_ZIPCOD ORDER BY M_ZIPCOD.STATE_CODE ASC;

Please advise. Thank you for your help.

Upvotes: 1

Views: 35

Answers (1)

Erik A
Erik A

Reputation: 32682

You can order by a comparison. Note that a comparison returns a boolean -1 = True, 0 = false, so if you order it ascending, you will get the results meeting the criterium first.

SELECT M_ZIPCOD.ZIP, M_ZIPCOD.CITY, M_ZIPCOD.COUNTY, M_ZIPCOD.STATE, 
M_ZIPCOD.STATE_CODE 
FROM M_ZIPCOD 
ORDER BY M_ZIPCOD.STATE_CODE > 1 ASC, M_ZIPCOD.STATE_CODE ASC;

I'm ordering by the comparison first, and then by the number, to order the zip codes in ascending order, the ones meeting the comparison first, then the ones failing the comparison in ascending order too.

Upvotes: 1

Related Questions