Re: Order By question


"Jan Hyde" <StellaDrinker@xxxxxxxxxxxxxxxxxxx> wrote in message
news:0cpgk256e3gvo7ooms6ldu2hjgp1jeula1@xxxxxxxxxx
"MP" <nospam@xxxxxxxxxx>'s wild thoughts were released on
Tue, 31 Oct 2006 14:46:34 -0600 bearing the following fruit:

vb6, ado, .mdb format, no access, win2k
sql newb <g>
assuming sql such as "Select F1, F2 From TableName Order by...."

with this order by statement
"order by F1, F2 DESC "

I don't really understand, you seem to want to order some of
the data one way and other data another.

J



AH HA! You *DO* understand!
:-)
Is there a way to do that via sql?

another way of saying the same thing...via very "pseudo" code
If F1 = '1n' Or F1 = '4B' then
'Order By F2'
Else
...some sql to produce the posted results for '2L' and '3R'
For Each Distinct F2
'Order By F1
Next F2
End If

thats' why i thought some kind of join(self join since it's only one table)
or transform or ?? might be required
except I can't figure out joins yet
I've read lots of examples but haven't figured out how to apply them to my
situation yet.
:-(

at the moment I'm getting various distinct recordset groupings and stepping
through w/ complex select case code to divide things up (it's *almost*
working)
just hoped there was some magic sql statement that would simplify the code

obviously this is an oversimplified snippet from the middle of a much larger
question
but if I could figure out how to do this...via the "ultra generous and
brilliant experts around here" help of course...<vbg>... then I think I can
manage from there.

Thanks for any ideas
Mark


the records are returned thus
F1 : F2

1N : 36
1N : 24
1N : 12

2L : 36
2L : 24
2L : 12

3R : 36
3R : 24
3R : 12

4B : 36
4B : 24
4B : 12


How can i change 'order by' statement (or create self-join) to return
thus

1N : 36<<<< orig order ok
1N : 24
1N : 12

<<<<<<<<<<<< here need to group by F1 first, then Order by F2 somehow
2L : 36
3R : 36

2L : 24
3R : 24

2L : 12
3R : 12
<<<<<<<<<<<<

4B : 36<<<< orig order ok
4B : 24
4B : 12

Thanks for any pointers
Mark



Jan Hyde (VB MVP)

--
The crooked Santa who broke out of jail might be called "an escape Claus."
(Renee from Napa)



.

Loading