October 22, 2013 at 1:25 pm
Hi,
I have a purchase orders table, which stores PurchaserID among others. The table has thousands of rows with several hundred different PurchaserID. The PurchaserID is same as the LoginID to the website. When the purchasers login they can see all the purchase orders, purchased by them as well as by others. I want to display all the orders purchased by the logged in user at the top. For example let us assume that there are 10 rows of PurchaserID = 123, and 10 rows of PurchaserID = 456 and another 10 rows of PurchaserID = 789. If the purchaser 456 logs in, then I want to show those records corresponding to PurchaserID = 456 at the top followed by others. Please let me know how this could be done in sql. I am using sqlserver 2008.
Thanks
October 22, 2013 at 1:32 pm
nadarajan_v (10/22/2013)
Hi,I have a purchase orders table, which stores PurchaserID among others. The table has thousands of rows with several hundred different PurchaserID. The PurchaserID is same as the LoginID to the website. When the purchasers login they can see all the purchase orders, purchased by them as well as by others. I want to display all the orders purchased by the logged in user at the top. For example let us assume that there are 10 rows of PurchaserID = 123, and 10 rows of PurchaserID = 456 and another 10 rows of PurchaserID = 789. If the purchaser 456 logs in, then I want to show those records corresponding to PurchaserID = 456 at the top followed by others. Please let me know how this could be done in sql. I am using sqlserver 2008.
Thanks
Sure you can do that. Something this should point you in the right direction.
order by case when PurchaserID = @LoggedIn then -1 else PurchaserID end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2013 at 2:07 pm
This is very useful answer. But I have a question. I believe -1 means it is not ordered. So should it be
order by case when PurchaserID <> @LoggedIn then -1 else PurchaserID end ?
Thanks
October 22, 2013 at 2:15 pm
nadarajan_v (10/22/2013)
This is very useful answer. But I have a question. I believe -1 means it is not ordered. So should it beorder by case when PurchaserID <> @LoggedIn then -1 else PurchaserID end ?
Thanks
No it means it will order by the PurchaserID, but when that value equals the same as current user it will replace that value with -1 for sorting purposes.
I used -1 because I assume your PurchaserID is a number and will never be less than 0.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 22, 2013 at 2:20 pm
Thanks, it worked perfectly!
October 22, 2013 at 2:25 pm
nadarajan_v (10/22/2013)
Thanks, it worked perfectly!
You are welcome. Glad that worked for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply