• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

Query Syntax - Ordering, Labels, Numbering

I have a query that pulls a small subset of values (names of assessment forms) from a large table containing lots of names we do not need.
I am using this query to create a drop down list for a report filter that I need to build.  I want to do "get from query", using this results set in specifying the filter's Available Values.
Query appears below.  How can I modify the syntax to end up where the choices appear in the order I want? I am assuming I'll need a label with numbering to make the list appear as shown below.

What I want to end up with is a results set that looks like so:
1. Consumer Needs Assessment
2. Nutritional Risk Assessment
3. Caregiver Assessment
4. Individual Client Contact Form
5. Public and Media Event Recording

I don't really care about showing or having the numbers appear in the drop down this will create unless I need them to force or support the ordering.

Here's the query:
HAR_ASSESSMENTS.ASSESSFORM_NAME
FROM
  HAR_ASSESSMENTS
WHERE
  HAR_ASSESSMENTS.ASSESSFORM_NAME = N'AAA Consumer Needs Evaluation'
    OR HAR_ASSESSMENTS.ASSESSFORM_NAME = N'Nutritional Risk Assessment'
    OR HAR_ASSESSMENTS.ASSESSFORM_NAME = N'Caregiver Assessment Form'
  OR HAR_ASSESSMENTS.ASSESSFORM_NAME = N'Individual Client Contact Form'
  OR HAR_ASSESSMENTS.ASSESSFORM_NAME = N'Public and Media Activity Recording'
GROUP BY
  HAR_ASSESSMENTS.ASSESSFORM_NAME
0
gberkeley
Asked:
gberkeley
2 Solutions
 
Ephraim WangoyaCommented:

Seems you already know the data you want to display so why do you need to select from a table

select N'AAA Consumer Needs Evaluation'
union all
select N'Nutritional Risk Assessment'
union all
select N'Caregiver Assessment Form'
union all
select N'Individual Client Contact Form'
union all
select N'Public and Media Activity Recording'
0
 
HainKurtSr. System AnalystCommented:
addition to what ewangoya posted

select * from (
select 1 id, N'AAA Consumer Needs Evaluation' ASSESSFORM_NAME
union all
select 2, N'Nutritional Risk Assessment'
union all
select 3, N'Caregiver Assessment Form'
union all
select 4, N'Individual Client Contact Form'
union all
select 5, N'Public and Media Activity Recording'
) x order by id
0
 
gberkeleyAuthor Commented:
Thanks for the lightning fast response!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now