T-SQL Problem

[Kim Pedersen [vbCode Magician]]

Hi

I have a small problem in an SQL query. I have a table containing some
users. Each user has five priorities (1 throught 5. Let's call them p1, p2,
p3, p4, p5). What I would like to do is select all users that have a p1, p2,
p3, p4 or p5 priority of let's say the string Programmer.. This is no
problem I have done it like this:

"SELECT * FROM tblUsers WHERE p1='programmer' OR p2='programmer' or p3=...."

Now what I would like to do is have the users that match this criteria
arranged so that users with p1 are ordered in front of users who only have
'programmer' as their second (p2) priority in the returned recordset and so
on. Basically what I need is an "ORDER BY" clause.

Could anyone help me out here??

Hilsen/Best regards
Kim Pedersen, vbCode Magician Host
http://hjem.get2net.dk/vcoders/cm
ICQ: 62990889

[Peter Vervoorn]

"Kim Pedersen [vbCode Magician]" <codemagician@nospam.get2net.dk> wrote in
message news:3a242131@news.devx.com...
> Hi
>
> I have a small problem in an SQL query. I have a table containing some
> users. Each user has five priorities (1 throught 5. Let's call them p1,
p2,
> p3, p4, p5). What I would like to do is select all users that have a p1,
p2,
> p3, p4 or p5 priority of let's say the string Programmer.. This is no
> problem I have done it like this:
>
> "SELECT * FROM tblUsers WHERE p1='programmer' OR p2='programmer' or
p3=...."
>
> Now what I would like to do is have the users that match this criteria
> arranged so that users with p1 are ordered in front of users who only have
> 'programmer' as their second (p2) priority in the returned recordset and
so
> on. Basically what I need is an "ORDER BY" clause.
>
> Could anyone help me out here??

You can use a union

SELECT field1, field2, fieldn, 1 as fieldRating FROM tblUsers WHERE p1 =
'programmer'
union
SELECT field1, field2, fieldn, 2 FROM tblUsers WHERE p2 = 'programmer'
union
..
..
SELECT field1, field2, fieldn, 5 FROM tblUsers WHERE p5 = 'programmer'
ORDER BY fieldRating

Good luck,

Peter Vervoorn
Software Engineer / Trainer

>
> Hilsen/Best regards
> Kim Pedersen, vbCode Magician Host
> http://hjem.get2net.dk/vcoders/cm
> ICQ: 62990889
>
>

[Bob Hollister]

"Kim Pedersen [vbCode Magician]" <codemagician@nospam.get2net.dk> wrote:
>Hi
>
>I have a small problem in an SQL query. I have a table containing some
>users. Each user has five priorities (1 throught 5. Let's call them p1,
p2,
>p3, p4, p5). What I would like to do is select all users that have a p1,
p2,
>p3, p4 or p5 priority of let's say the string Programmer.. This is no
>problem I have done it like this:
>
>"SELECT * FROM tblUsers WHERE p1='programmer' OR p2='programmer' or p3=...."
>
>Now what I would like to do is have the users that match this criteria
>arranged so that users with p1 are ordered in front of users who only have
>'programmer' as their second (p2) priority in the returned recordset and
so
>on. Basically what I need is an "ORDER BY" clause.
>
>Could anyone help me out here??
>
>Hilsen/Best regards
>Kim Pedersen, vbCode Magician Host
>http://hjem.get2net.dk/vcoders/cm
>ICQ: 62990889
>
>
You may wish to re-think your data model to achieve your goal.
I would create a table called "UserPriorities" whose foreign key is the primary
key of your "users" table, as such:

UserID Priority Value
125 p1 programmer
125 p2 net admin
130 p1 programmer
130 p2 net admin
130 p3 business manager

Your SQL would have the syntax of "SELECT * FROM UserPriorities WHERE Value
= 'programmer' ORDER BY Priority"

There are other ways to do it programmatically but they represent very contrived
solutions. Because a user can have a collection of "priorities," a "userpriorities"
table that is keyed to the user table is the most professional approach since
it correctly normalizes the data in the database.