Numerical Order

[Windsor302]

Hey guys,

I would like a filter that when I sort ascending, will put the values in order(1,2,3..) be it in a table or query. Thanks!

 

[tehNellie]

Use the sort in query design view on the column in question

in SQL

SELECT column
FROM table
ORDER BY NumberColumn (DESC)

The ORDER BY Defaults to ascending order but DESC tells it to sort descending.

A Table should not be regarded as having a sort order. If you make a number column the primary key it will sort on that column, but sort order is not a basis to decide where you put indexes in my opinion.

 

[Windsor302]

Here is the code:

SELECT DISTINCT TABLE.[JOB_No], TABLE.[COMPLETED_DATE], TABLE.[ACCEPT], TABLE.[REJECT]
FROM TABLE;

Where would i put the:

ORDER BY NumberColumn (DESC)

Thanks.

 

[tehNellie]

SELECT TABLE.[JOB_No], TABLE.[COMPLETED_DATE], TABLE.[ACCEPT], TABLE.[REJECT]
FROM TABLE;
[b]ORDER BY table.[job_no][/b]

You shouldn't need SELECT DISTINCT in this context as there shouldn't be duplicate records.

 

[Windsor302]

That does not seem to be doign anything

SELECT DISTINCT HOTBIT.[WELD_No], HOTBIT.[WELD_DATE], HOTBIT.[RT_ACCEPT], HOTBIT.[RT_REJECT]
FROM HOTBIT
ORDER BY HOTBIT.[WELD_NO]

 

[The_Doc_Man]

If Weld_No is already the prime key of the HotBit table, the ORDER BY clause will have no effect since sorting by prime key is automatic.

 

[Windsor302]

This is not the Primary key

 

[The_Doc_Man]

If the field is not numeric, an ORDER BY might product an order that is more text-like than number-like.

In this case, the order would be 1, 10, 11, 12, 2, 20, 21, ...

Despite the large number of options, you really only have a few possible sorts for a given field. It will either be numeric or text-oriented.

By the way, just for short-cut purposes, you can take this:

SELECT DISTINCT HOTBIT.[WELD_No], HOTBIT.[WELD_DATE], HOTBIT.[RT_ACCEPT], HOTBIT.[RT_REJECT]
FROM HOTBIT
ORDER BY HOTBIT.[WELD_NO]

And turn it into this with no loss of meaning or accuracy:

SELECT DISTINCT [WELD_No], [WELD_DATE], [RT_ACCEPT], [RT_REJECT]
FROM HOTBIT
ORDER BY [WELD_NO] ;

You can do this because it is a single-table query. This would not be the same for a multi-table query involving a join, but for this case you should be OK.

 

[Windsor302]

Thanks. So we are not able to make the values apear in numerical order then.

 

[tehNellie]

Yes, it's called ORDER BY, there's a whole thread around here somewhere that explains in detail and with numerous examples and considerations how to sort data, I wonder if I can find it....

 

[Windsor302]

That would be great, thans.

 

[tehNellie]

There needs to be a sarcasm smiley. You're in the thread that explains how to sort things, the person not giving much information away so far is you.

"So you can't sort stuff" isn't much to go on, not to mention inaccurate.

If you've set the column to text then sorting will act differently because you're not sorting numbers, you're sorting text and 10 comes before 2 as a text value.

 

[jdraw]

windsor302,
It would be helpful to those responding and those reading, if you would describe your issue(s) to help put some context on the question.
I was having difficulty when you went from

TABLE.[JOB_No], TABLE.[COMPLETED_DATE],...

to

HOTBIT.[WELD_No], HOTBIT.[WELD_DATE], HOTBIT.[RT_ACCEPT], HOTBIT.[RT_REJECT]
FROM HOTBIT....

and it wasn't doing anything in a post following a meaningful response.

We only know what you tell us about your situation, and you haven't said much.