| Author |
 Topic Search  Topic Options
|
Scotty32 
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: how do i order a RS in a sepcial way
Posted: 29 October 2003 at 12:41pm |
ok i wanna start off by sayin i had no idea wot to put the "subject" of this as so i put it as it is now
ok wot i want to do is
SELECT * FROM table ORDER BY (field1 - field2) DESC
i have a freestyle battle site and would like to list the top battlers
i have a Wins field and a Losses field... so i want to take the Losses away from the wins when ordering
this will mean sumone with "3 wins" and "2 loss" will below sumone with "2win" and "0 losses"
does anyone know if this or how this is possable
i tried doing wot i did above, but it just decided it would order by the Wins DESC - which works for now, but isnt wot i need
|
 |
fernan82
Mod Builder Group
Joined: 17 November 2002
Location: United States
Status: Offline
Points: 362
|
Post Options
Thanks(0)
Quote Reply
Posted: 29 October 2003 at 10:41pm |
|
On msSQL you can add a new field called field3 with a formula (field1 - field2), then just order the records by field3.
On access you can do something similar, create a field3 for the formula but you'll have to update the field with ASP everytime you update field1 or field2...
I think besides that all you can do with access is pull all the records into an array and sort them somehow with ASP, with msSQL you might be able to use a stored procedure but still the above method would be more effective...
|
FeRnAN
|
|
JoeP
Newbie
Joined: 10 October 2003
Location: United States
Status: Offline
Points: 38
|
Post Options
Thanks(0)
Quote Reply
Posted: 31 October 2003 at 2:22pm |
SELECT * FROM table ORDER BY [LOSSFIELD] ASC [WINFIELD] DESC
This may give you results you wanted. I did a sample in excel with that sort and got:
| wins |
losses |
| 2 |
0 |
| 2 |
0 |
| 3 |
1 |
| 2 |
1 |
| 3 |
2 |
| 1 |
2 |
| 0 |
3 |
| 5 |
4 |
|
|
Scotty32
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
Post Options
Thanks(0)
Quote Reply
Posted: 31 October 2003 at 2:37pm |
thanks for the help, but am just gunna forget it untill i get a SQL server, since that sounds like it'll work
JoeP i think that will work the same as wot i have now:
SELECT * FROM table ORDER BY [WINFIELD] DESC [LOSSFIELD] ASC
but it doesnt work right, becose as you can see in your post, "5-4" is below "0-3" which doesnt make sence, but thanks anyway
cept i guess mine would be worse and it would put "5-4" at the top 
there HAS To be a way around this
|
|
Flamewave
Senior Member
Joined: 19 June 2002
Location: United States
Status: Offline
Points: 376
|
Post Options
Thanks(0)
Quote Reply
Posted: 31 October 2003 at 3:35pm |
You may be able to do something like this with access...i havnt tried it to see if it works in access, but I know it works with SQL:
SELECT *, (field1 - field2) AS sorder FROM table ORDER BY sorder DESC
|
|
- Flamewave
They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.
|
|
Scotty32
Moderator Group
Joined: 30 November 2002
Location: Manchester, UK
Status: Offline
Points: 1682
|
Post Options
Thanks(0)
Quote Reply
Posted: 31 October 2003 at 4:17pm |
either i did it wrong, or it doesnt work 
got this error:
Microsoft JET Database Engine error '80040e14'
Undefined function 'tblAuthor.BattleLoose' in expression.
/forum/default.asp, line 204
(oh i just wanna add.... i seen sum messages from people who expect people to the work for them, (not naming names) and sum people have started to refuse to help, but am not askin ya to do the work for me, i just wanna know how the hell to get this to work, ive tired it my self and i have no idea where to look, this is the only site i know where i can get help, and i do help other people if i can, so could ya please help me , just felt i had to had this)
|
|
Flamewave
Senior Member
Joined: 19 June 2002
Location: United States
Status: Offline
Points: 376
|
Post Options
Thanks(0)
Quote Reply
Posted: 31 October 2003 at 5:02pm |
|
It doesn't look like access suports it then. I just tried running it in access and it didn't work. At least you can use it when you get MS SQL though.
|
|
- Flamewave
They say the grass is greener on the other side, but if you really think about it, the grass is greener on both sides.
|
|
