Select Problem

[lah3233]

Is there anyway in a select statment to list 5 fields but only display 3 of them? I need some of the fields for the order by clause but don't actually want them displayed. Is there a solution?

 

[SonOfEmidec1100]

Just dont select them, or is that an over simplification ?

Select field1,field2,field3 ORDER BY field1,field2,field3,field4,field5

 

[lah3233]

That's an oversimplification....it won't allow us to do the order by without selecting all the fields

 

[SonOfEmidec1100]

My suggestion is a valid sql statement, whats IT in "it wont allow us" ?

 

[espar]

You could create a view which has all 5 fields then use a select statement with the 3 you want from the view.

 

[JamesLean]

What error are you getting? SonOfEmidec1100's solution is perfectly valid. ORDER BY columns don't necessarily have to appear in the SELECT list.

Are you using a GROUP BY? If so, then you would have to include the ORDER BY items in that.

Can you post your actual query if you're still having problems.

--James

 

[lah3233]

Here's the statement. There is a nested SQL statement in the order by:

SELECT (strLName + ', ' + strFName) as name
FROM UserInfo, LoginInfo
WHERE UserInfo.strUserID = LoginInfo.strUserID
AND strRights = 'user'
AND (strUserStatus = 'active'
OR strUserStatus = 'inactive')
ORDER BY (SELECT strLName + ', ' + strFName
FROM UserInfo
WHERE strAdminID = UserInfo.strUserID) ASC,
strLName, strFName, strUserStatus


What essentially we want to happen is just change the order by clause around depending on how the user clicks to sort the data. Does this make sense??

Thanks for all the helP!

 

[JamesLean]

I'm struggling to make sense of this to be honest. Can you describe the tables you are using, the relationships between them and what results you want to see?

--James

 

[lah3233]

It's somewhat complicated....but I think we are going to try using views and see where we can get from there.