Order By problem

[CJM]

I have a table with a 'rank' column. This column is use to record the top 3
ranked records - ie the user enters a 1,2,3 or blank against each record.

I want to list the top three in order, followed by the non-ranked records in
any order.

A further complication is that this must work for Access 2k and SQL Server
2k..

Any ideas?

Thanks

Chris

[Bob Barrows [MVP]]

CJM wrote:

> I have a table with a 'rank' column. This column is use to record the
> top 3 ranked records - ie the user enters a 1,2,3 or blank against
> each record.
>
> I want to list the top three in order, followed by the non-ranked
> records in any order.
>
> A further complication is that this must work for Access 2k and SQL
> Server 2k..
>
> Any ideas?
>
> Thanks
>
> Chris

You could use a union query:

select 'ranked' as Source, rank,<rest of fields>
from table where rank is not null
Union all
select 'unranked' as Source, rank,<rest of fields>
from table where rank is null
Order By Source, rank

HTH,
Bob Barrows


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[CJM]

Thanks Bob.

I haven't actually got it working yet, but I've developed some SQL based on
you suggestion that seem to do the job I want.

Tx

Chris

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:OXeGFf89DHA.4020@TK2MSFTNGP09.phx.gbl...

> CJM wrote:

> > I have a table with a 'rank' column. This column is use to record the
> > top 3 ranked records - ie the user enters a 1,2,3 or blank against
> > each record.
> >
> > I want to list the top three in order, followed by the non-ranked
> > records in any order.
> >
> > A further complication is that this must work for Access 2k and SQL
> > Server 2k..
> >
> > Any ideas?
> >
> > Thanks
> >
> > Chris

>
> You could use a union query:
>
> select 'ranked' as Source, rank,<rest of fields>
> from table where rank is not null
> Union all
> select 'unranked' as Source, rank,<rest of fields>
> from table where rank is null
> Order By Source, rank
>
> HTH,
> Bob Barrows
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

[Bullschmidt]

<<
I have a table with a 'rank' column. This column is use to record the
top 3
ranked records - ie the user enters a 1,2,3 or blank against each
record.

I want to list the top three in order, followed by the non-ranked
records in
any order.

A further complication is that this must work for Access 2k and SQL
Server
2k..

Any ideas?

Thanks

Chris

>>

How about something like this:

SELECT * FROM MyTable ORDER BY IsNull(Rank) DESC, Rank

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[CJM]

Bob,

I've got your solution working with SQL Server, but not with Access.

My SQL query is:

Select 'Ranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
H.HotelID, H.Rank
from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
Inner Join Locations L on P.LocID = L.LocID
where L.LocID = 2 and H.rank is not null
Union
Select 'Unranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
H.HotelID, H.Rank
from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
Inner Join Locations L on P.LocID = L.LocID
where L.LocID = 2 and (H.Rank is Null or H.Rank = 0)
Order By Source, H.Rank

For context, I am linking a table of hotels with a table of locations. The
PrefHotels table links the hotels to appropriate locations (ie allows a
many-to-many relationship)

With Access, I get the following error:

'The ORDER BY expression (H.Rank) includes fields that are not selected by
the query. Only those fields requested in the first query can be included in
an ORDER BY expression.'

Any ideas?

Thansk

Chris

[Bob Barrows]

CJM wrote:

> Bob,
>
> I've got your solution working with SQL Server, but not with Access.
>
> My SQL query is:
>
> Select 'Ranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
> H.HotelID, H.Rank
> from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
> Inner Join Locations L on P.LocID = L.LocID
> where L.LocID = 2 and H.rank is not null
> Union
> Select 'Unranked' as Source, H.HotelName, H.TelNo, H.URL, H.Location,
> H.HotelID, H.Rank
> from (Hotels H Inner Join PrefHotels P on H.HotelID = P.HotelID)
> Inner Join Locations L on P.LocID = L.LocID
> where L.LocID = 2 and (H.Rank is Null or H.Rank = 0)
> Order By Source, H.Rank
>
> For context, I am linking a table of hotels with a table of
> locations. The PrefHotels table links the hotels to appropriate
> locations (ie allows a many-to-many relationship)
>
> With Access, I get the following error:
>
> 'The ORDER BY expression (H.Rank) includes fields that are not
> selected by the query. Only those fields requested in the first query
> can be included in an ORDER BY expression.'
>
> Any ideas?
>

Yes. Don't use the table alias in the ORDER By clause. With a union query,
the only fields the "order by" knows about are the field names coming from
the first select statement. It knows nothing about the data sources in that
select statement. So go back to my initial suggestion and simply say:

Order By Source, Rank

This will work in both Access and SQL Server

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

Bullschmidt wrote:

> <<
>
> A further complication is that this must work for Access 2k and SQL
> Server
> 2k..
>
>
> How about something like this:
>
> SELECT * FROM MyTable ORDER BY IsNull(Rank) DESC, Rank
>

IsNull, while it is a VBA function, does not do the same thing as the SQL
Server ISNULL() function.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[CJM]

OK, Bob... I've taken your suggestion...

I'm now getting an error on the next line:

"No value given for one or more required parameters."
on
"Set rsInfo = oConn.Execute (sSQL)"

[This occurs for the Access DB version only]

Any further ideas?

Cheers

[CJM]

If it's any use, I've just tried entering the SQL that my ASP produces into
a query in Access itself, and it works... so the problem is presumably with
ASP/ADO rather than Access/SQL...

CJM

"CJM" <cjmwork@yahoo.co.uk> wrote in message
news:OD9mOIj%23DHA.2808@TK2MSFTNGP10.phx.gbl...

> OK, Bob... I've taken your suggestion...
>
> I'm now getting an error on the next line:
>
> "No value given for one or more required parameters."
> on
> "Set rsInfo = oConn.Execute (sSQL)"
>
> [This occurs for the Access DB version only]
>
> Any further ideas?
>
> Cheers
>
>
>
>

[CJM]

<! #include file="expletives.inc">

Er... I still had a H.Rank in the query somewhere... oops!

All seems to be working.. finally.

Thanks

Chris

"CJM" <cjmwork@yahoo.co.uk> wrote in message
news:OWtRqMj%23DHA.2064@TK2MSFTNGP11.phx.gbl...

> If it's any use, I've just tried entering the SQL that my ASP produces

into

> a query in Access itself, and it works... so the problem is presumably

with

> ASP/ADO rather than Access/SQL...
>
> CJM
>
> "CJM" <cjmwork@yahoo.co.uk> wrote in message
> news:OD9mOIj%23DHA.2808@TK2MSFTNGP10.phx.gbl...

> > OK, Bob... I've taken your suggestion...
> >
> > I'm now getting an error on the next line:
> >
> > "No value given for one or more required parameters."
> > on
> > "Set rsInfo = oConn.Execute (sSQL)"
> >
> > [This occurs for the Access DB version only]
> >
> > Any further ideas?
> >
> > Cheers
> >
> >
> >
> >

>
>