| Author |
Topic |
|
rtown
Yak Posting Veteran
53 Posts |
 Posted - 2010-08-16 : 17:12:07
|
Hello, I am trying to use ORDER by on a query. I want to sort by date but the order doesnt seem to do anything at all.ORDER BY QuoteDate Where "QuoteDate" is a Datetime field.What gives? Tried ASC and DESC as well... nothing. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-08-16 : 17:36:25
|
| Can you post your entire query? It should work just fine. |
 |
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-16 : 17:39:46
|
| Sure!SELECT TOP 100 PERCENT Quote, Customer, JobName, FollowUp, QuoteStatus, QuoteDate, ActiveFROM dbo.JobsWHERE (Quote LIKE 'A%') AND (QuoteStatus = 'Low Bid - Order Pending' OR QuoteStatus IS NULL) AND (Active IS NULL)ORDER BY QuoteDateBy the way, I have no idea what this "TOP 100 PERCENT" is... Access adds that automatically. |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-16 : 17:48:18
|
| By the way, the ORDER by clause I posted above works fine in ASP for this same query... just not in access. ADP project, sqlexpress 2008. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-16 : 21:15:00
|
If the code above is part of a view, and you do select against the view, the order is not there anymore for sql server 2005 and later.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-08-17 : 02:31:10
|
| Are you using as part of a subquery. Why did you use top 100 percent?MadhivananFailing to plan is Planning to fail |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-17 : 10:14:01
|
| As I said, the "top 100 percent" is added automatically by access. I do not know why it is there and access insists on adding it. And Peso, im not sure what you mean by part of a view... i dont really know what that means. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 10:26:43
|
| where are you using this query in access?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-17 : 10:59:37
|
| In access, its just a regular query. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2010-08-17 : 11:15:33
|
As a query? As code? Or what!?
N 56°04'39.26"E 12°55'05.63" |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-17 : 11:32:07
|
| I dont understand the question... its a query in access.. you double click it and it returns results. please explain better. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 11:34:33
|
| double click where?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-17 : 11:51:44
|
| on the query name... under "queries" on the left side in this program called microsoft access... you may not have hear of it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 11:59:55
|
| The problem, I believe, in Access is that you can "query a query".Translating that into SQL speak Access is probably making the second query into a VIEW.A VIEW cannot contain an ORDER by - so you cannot do:QUERY (Inner QUERY with ORDER BY statement)but you can doQUERY (Inner Query) ORDER BY Col1, Col2Access is probably making all such "queries" into VIEWs within SQL.So if your ORDER BY is on an "inner" Query / View it will be "ignored" by SQL when it prepared the Outer query.So ... your outer-most query will have to have the "final" ORDER BY statement. |
|
|
|
rtown
Yak Posting Veteran
53 Posts |
Posted - 2010-08-17 : 12:03:26
|
| Kristen: thank you so much for the clear explanation. Some people here apparently enjoy making people who are trying to learn this stuff feel stupid. Hopefully there are more like you here. Thank you. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 12:15:41
|
quote:
Originally posted by rtown
Kristen: thank you so much for the clear explanation. Some people here apparently enjoy making people who are trying to learn this stuff feel stupid. Hopefully there are more like you here. Thank you.
Can you state one instance when somebody did that to you? Did you see Peso asking you whether it was inside view? How could somebody understand what you're trying to do without asking the details? First understand that people are not here just because they want to spent sometime making fun of others but rather they are here to help out others to solve their problems by giving their suggestions in best possible way; that too spending some of their free time without any benefits whatsoever.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 12:19:19
|
" Some people here apparently enjoy making people who are trying to learn this stuff feel stupid."This is a MICROSOFT SQL SERVER forum site. People here will know nothing/little about Access. I certainly don't. If you say things like "you double click it and it returns results" it means nothing to folk who don't know Access. People who go to the trouble of constructing clear questions get far better service here in my experience.If my explanation is correct please note that Peso provided the correct answer in his response to your second post (when you provided the query as requested) the only problem was that you didn't understand it (fair enough), and I reckon for the rest of the thread here folk didn't understand what you were saying.There is a Access forum here, although it may have a limited audience, but folk there are more likely to have some understanding of Access - at the very least they should make the assumption (not guaranteed!) that your question is about Access  |
|
|
|
|
ORDER by Date not working...