| Author |
Topic |
|
pamyral_279
Posting Yak Master
143 Posts |
 Posted - 2013-02-26 : 11:30:40
|
| Hi all,I have a table with many rows , I need to change position of rows such as :Id Name Class Country1---A-----U11---US2---B-----U12---AR3---C-----U13---UK4---D-----U15---TH.....Row has Id is one change into row with id is three. Output :Id Name Class Country3---C-----U13---UK2---B-----U12---AR1---A-----U11---US4---D-----U15---THThank you very much ! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-02-27 : 20:47:24
|
| Have you ways to implement my idea by using "Order by" ?Thank you very much ! |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-27 : 22:55:15
|
yep...but you dont refer to that as "change of position" as it doesnt make any change in way row is stored in table. Its just sorting of results after retrieving from the tableSELECT *FROM TableORDER BY CASE WHEN Id = 3 THEN 1 WHEN Id=4 THEN 3 ELSE 2 END,Id DESC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-02-28 : 12:56:39
|
I want to find "general mode" to sort all rows in table ! Mean that I can order all rows in table on ways I like.May be , my table don't have "Order" ColumnId Name Class Country Order3---C-----U13---UK------11---A-----U11---US------22---B-----U12---AR------34---D-----U15---TH------4In real, my table has 1000 rows or more , how can I add order column automatically ! Such as when I set Order Column of fourth rows with value is 1 , it will display the first !Id Name Class Country Order4---D-----U15---TH------13---C-----U13---UK------21---A-----U11---US------32---B-----U12---AR------4How can I do that ? Let me know explain more detail if you don't understand !Thank you very much !quote:
Originally posted by visakh16
yep...but you dont refer to that as "change of position" as it doesnt make any change in way row is stored in table. Its just sorting of results after retrieving from the tableSELECT *FROM TableORDER BY CASE WHEN Id = 3 THEN 1 WHEN Id=4 THEN 3 ELSE 2 END,Id DESC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-28 : 13:01:54
|
| how do you pass the order column value? as a parameter?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-03-01 : 10:42:15
|
I will design order column as physical design ! It will solve my problem ! But I can not write store procedure to re-write order column when swap out two items !Have you ideas this problem ?quote:
Originally posted by visakh16
how do you pass the order column value? as a parameter?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-01 : 12:42:27
|
| i dont understand your requirement. so is it just swapping of two rows and keeping others as is.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-03-03 : 23:58:28
|
Ok ! Let me explain more clearly !+ Requirement :I have a category which contain more articles , such as sport,economic,fashion ...Mean that,at this time, article A will be published on top of category (Top one ) ! after that , article B will be published on top of category.So that article A will be top two about position.Due to article A is "hot" article , I want to keep position in top one.That is reason why i want to swap two rows.Have you other ways to do my idea ?Thank you very much !quote:
Originally posted by visakh16
i dont understand your requirement. so is it just swapping of two rows and keeping others as is.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-04 : 04:22:53
|
| there's no concept of ordering while storing data in a table. You will be doing ordering only when you fetch from table. So how do you determine hot article? is it by means of flag or is there another field which helps you to identify "hot" ones?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-04 : 07:19:37
|
| Pamyral, as long I understaind, i think you need to add a new column (e.g. pageOrder) which would be integer and that will be deciding where to place a specific record/information in a page. For exampleID Name PageOrder1 Category1 1002 Category2 200N CategoryN 300In case if you need to keep CategoryN on top then make necessary adjustment in the PageOrder. But this is kind of Manual effort and this concept is applied when there is no frequent changed or your selection is not dependent on the underlaying data e.g. alphabetical categories or location etc. CheersMIK |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2013-03-04 : 12:01:45
|
it's been said before on this post but:in a relational database a table has no order. only when you get results can you order them.You will quickly find that maintaining your own order number will become painful -- you will have to manage cases when multiple people insert into the table -- which one is the correct ordernumber? What do you do when you delete entries? do you reorder all the others?Also this is a *display* issue. So if you *are* going to try doing this -- I'd make a new table that has an ordernumber column and links to the key of this table.then join to it and derive the order that way -- use LEFT JOIN so anything not ordered can go at the bottom.or just don't do this. I don't understand why you'd want to when there is no natural SORT to the data you want to use.Transact CharlieMsg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. http://nosqlsolution.blogspot.co.uk/ |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-04 : 12:47:18
|
| Per requester comments 03/03/2013 : 23:58:28, it seems that articles are changing based on their popularity (or based on any other requirements) at a certian time. So the question is how to determine Hot Aritcles.One possible way could be to add a new column that will have ordering inforamtion and would be updated manualy per requirements. YES, this would be tedious if there are frequent updates (hourly/dialy) and I did mention this in my response. but might be acceptable for quaterly/semi-annual/annual period. Now if the change is on hourly/daily basis then I don't think of a way to acheive it marely with the help of a SQL query. In that case, a possible solution would be to have a field/Column which will be updated (automatically using a tool/script/trigger etc) on periodic basis and its asc/desc value will decide where to put an article on the page.CheersMIK |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-04 : 22:57:37
|
| or there can be other criteria based on some calculations on other fields to determine hot articles which got sold most, which got best most number of positive feedback etc. In that case, I think there would be an existing field which you can utilize to get this info and get hot ones if you define a threshold.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-03-05 : 02:32:46
|
| Thank you all : visakh16, MIK_2008 ,Transact CharlieMIK is said right, i need to columnn "Order" to add my table. So I redesign my tables as :Article Tables :Id_Article---ArticleName--CategoryId--Order--Time50--------------D-------------1--------1-----.....53--------------B-------------1--------2-----.....60--------------C-------------1--------3-----.....68--------------A-------------1--------4-----.....Hot article is identified by published time , in this case D article is hot article.(nearest current time)At 5:00 pm, C will be consider as hot article , so it is top one ( May be run store procedure to make C as hot article)50--------------c-------------1--------1-----.....53--------------D-------------1--------2-----.....60--------------B-------------1--------3-----.....68--------------A-------------1--------4-----.....At 8:00 pm, A will be consider as hot article , so it is top one .( May be run store procedure to make A as hot article)50--------------A-------------1--------1-----.....53--------------C-------------1--------2-----.....60--------------D-------------1--------3-----.....68--------------B-------------1--------4-----.....If new records insert table , in default, it is always in top one !Please help me to solve my scenario ! Thank you all everybody ! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-05 : 04:00:25
|
Whatever you're asking for doesnt make sense. How will other field values remain as is when you swap Article? when you shuffle records across, the other values (values for Id_Article,CategoryId etc will also be varying. you can have a row change value for column alone while orderingIf it was a typo, then you can try belowSELECT *FROM TableORDER BY DATEDIFF(ss,[Order--Time],GETDATE()) ASC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-05 : 05:31:16
|
| First of all do not directly add column to the table in production unless you are sure of its usage. Since those were simply ideas based on assumptions. Well so far, we are unable to understand the logic you're trying to explain. I've questions on the one you just explained. 1) You said "Hot article is identified by published time , in this case D article is hot article.(nearest current time)". This means that any article that is newly added is considered to be a HOT article. In such case you don't need to add any new field .. simply use a date column that has "row insert" date information and use it in Desc order to get aritlces that are newly added. 2) Then you said "At 5:00 pm, C will be consider as hot article , so it is top one ( May be run store procedure to make C as hot article)". This means you're contradicting to your first statement where you mentioned that Newly added articles will be considered hot articles and should be listed on top. Since I see that C was already there in the first scenario, meaning that C is not a new Article .... Then my question is Why it gets HOT article? Is it just because of the Time "5 PM"?3) Then you said "At 8:00 pm, A will be consider as hot article , so it is top one .( May be run store procedure to make A as hot article)". This again constradicting to what you said in point 1. 4) in last you said "If new records insert table , in default, it is always in top one". Again a contradiction. I hope you understand what I meant by all this .. you need to be clear on what you need and make us clear how exactly articles are decided to be HOT :)Is it so that D gets hot article because it has been added newly... then C gets hot article because there has been some updates/changes to the record AFTER D is added and become a hot article....then A gets hot article because of some updates/changes/operations that occured after A? OR ......? GrrrrrrrrrrrCheersMIK |
|
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-03-05 : 11:53:08
|
| Ok ! Thank for your replies !I take example for you : "http://edition.cnn.com/BUSINESS/?hpt=hp_bn1"Business is category of cnn website ! You will see "Top business stories" in page .TOP BUSINESS STORIES 1. Ferrari unveils V12 hybrid in Geneva 2. Report: Apps industry surging to $25B 3. Why China needs to spend more on welfare 4. Tomb Raider's Lara Croft is back 5. Investors click out of Apple for Google 6. Boeing ready to move on Dreamliner fix 7. Struggling in your career? Good 8. China eclipses U.S. as top oil importer 9. 'Peak oil' doomsayers proved wrong?It includes nine articles ( it may be sort base publish time ).I take a example, at 5:00 pm, title of " Boeing ready to move on dreamliner fix" is important news.I want to put on top one ? How you do that ? At 8:00 pm , news : "China eclipses U.S .. " was pushed third position instead of "Why China needs to spend more on welfare" ? So, how you do that ?Please give me the way to implement this scenario ? I hope you understand . Please let me know if you have other opinions !Thank you very much ! |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-05 : 12:13:11
|
| It shows that 1) new record is not the criteria to have a story to be on the top2) time is not a factor for a stroy to be on the top, since next day that would be an old story and thus not required to be on the page even. If answer to both point is yes, then it means that there is no exact criteria for ordering (in other words deciding about HOT article). So I think you would need a new column, which will need to be handled manually in order to show the desired articles in required ordering. Since on a page its not possible to have more than a limited number of articles be display so it would not be that tedious. For example, CNN website publish only 10 articles on the main page, haing the hottest one on the top and then the second one and then the third one so on.... What you can do is to have a new field SET the ArticleOrder (the new) column with required values e..g Hottes one would be 1, 2nd one would be 2 and so on... this way you will need only to update ten records at a time (when required) and let the value of this new field for all other articles as NULL. While pulling information for the main page on CNN website query for the aritcles having non-null values in ascending order and get them displayed on the page. next time after an hour you need to reupdate 10 new hot articles then update the new column = null for all records where ArticleOrder is not null, and refill it with desired sequence. Second approach could be to have a sort of temp table having a ArticleOrder column as well, inserting desired records into it and displaying the hottest articles using this temp table ... and so on .. hope this helps CheersMIK |
|
|
|
pamyral_279
Posting Yak Master
143 Posts |
Posted - 2013-03-05 : 21:22:27
|
| Your second approache is good option ! So, can you write procedure or simulation code for temp table which will sort ?Thank you very much ! |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2013-03-06 : 04:21:56
|
| well attemp yourself and if you face any issue you can ask for help from SQLTeam. thanks!CheersMIK |
|
|
|
Next Page
|
Question about change position of rows