Always Select a specific job title to be the first record in a query (1 Viewer)

[John Lee]

Hi I want to be able to always have the Manager job titles in each of my queries to be the first record, any ideas on how to achieve that,

any assistance appreciated.

 

[MajP]

Select field1, field2....1 as Sort from SomeTable where JobTitleID = SomeID
UNION
Select field1, field2....2 as Sort from SomeTable where JobTitleID <> SomeID
Order by N, Y

' where N is the index of the sort field and Y is the index of other fields.

 

[Gasman]

Give the titles a sort order and use that?

 

[The_Doc_Man]

@MajP, doesn't that give back the manager's row twice?

 

[The_Doc_Man]

This is an example of knowing ahead of time that you are planning to do something and including it in the plans. If you know that you have special and persistent sort requirements, you need to have a field in the table to provide a sort order. Your report certainly can sort by something that it doesn't actually display as long as the sort field is in the supporting query.

In order for us to know how to best support this requirement we would need to see some hint as to the structure of your table or tables. The optimum place for this possible sort field depends on how your tables are laid out.

 

[CJ_London]

insufficient information provided - all depends if the job title is text or numeric (as in a FK) and if so whether the jobtitles table has a sort order field

 

[MajP]

Pro

@MajP, doesn't that give back the manager's row twice

Thanks. Modified it now. Actually this question is so poor I have no idea what it is really asking.

Manager job titles in each of my queries to be the first record

Always Select a specific job title to be the first record

The title says a Specific job title, vs order by job titles.

 

[Mark_]

Second having a "Sort order". I have a lookup table that uses this method as the data for some lookups doesn't natively make sense sorted alphabetically. For example "Spring 2025" comes after "Fall 2025" if you just do it alphabetically. Likewise "Spring 2024" comes after "Fall 2025"!

 

[Pat Hartman]

If something that indicates "Manager" is in one of the tables from which you are selecting the data, sort first by that value, then by whatever is meaningful with in the initial group.

 

[cheekybuddha]

any assistance appreciated.

It would really help if you described your table (field names, datatypes, indexes), and same for the 'each of my queries' (perhaps post their SQL).

Generally you can do something like:

SELECT
  ID,
  JobTitle
FROM JobTitles
ORDER BY
  JobTitle LIKE '*manager*',
  JobTitle
;