SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Question about change position of rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pamyral_279
Posting Yak Master

129 Posts

Posted - 02/26/2013 :  11:30:40  Show Profile  Reply with Quote
Hi all,
I have a table with many rows , I need to change position of rows such as :
Id Name Class Country
1---A-----U11---US
2---B-----U12---AR
3---C-----U13---UK
4---D-----U15---TH
.....

Row has Id is one change into row with id is three.
Output :
Id Name Class Country
3---C-----U13---UK
2---B-----U12---AR
1---A-----U11---US
4---D-----U15---TH


Thank you very much !

Lamprey
Flowing Fount of Yak Knowledge

4600 Posts

Posted - 02/26/2013 :  11:49:22  Show Profile  Reply with Quote
Rows don't have positions. You can use an ORDER BY clause on your query:
http://msdn.microsoft.com/en-us/library/ms188385.aspx
Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 02/27/2013 :  20:47:24  Show Profile  Reply with Quote
Have you ways to implement my idea by using "Order by" ?
Thank you very much !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/27/2013 :  22:55:15  Show Profile  Reply with Quote
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 table


SELECT *
FROM Table
ORDER BY CASE WHEN Id = 3 THEN 1 WHEN Id=4 THEN 3 ELSE 2 END,Id DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 02/28/2013 :  12:56:39  Show Profile  Reply with Quote
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" Column

Id Name Class Country Order
3---C-----U13---UK------1
1---A-----U11---US------2
2---B-----U12---AR------3
4---D-----U15---TH------4

In 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 Order
4---D-----U15---TH------1
3---C-----U13---UK------2
1---A-----U11---US------3
2---B-----U12---AR------4


How 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 table


SELECT *
FROM Table
ORDER BY CASE WHEN Id = 3 THEN 1 WHEN Id=4 THEN 3 ELSE 2 END,Id DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/









Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/28/2013 :  13:01:54  Show Profile  Reply with Quote
how do you pass the order column value? as a parameter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 03/01/2013 :  10:42:15  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/01/2013 :  12:42:27  Show Profile  Reply with Quote
i dont understand your requirement. so is it just swapping of two rows and keeping others as is.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 03/03/2013 :  23:58:28  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/



Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/04/2013 :  04:22:53  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  07:19:37  Show Profile  Reply with Quote
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 example

ID Name PageOrder
1 Category1 100
2 Category2 200
N CategoryN 300

In 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.



Cheers
MIK
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 03/04/2013 :  12:01:45  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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 Charlie
Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
http://nosqlsolution.blogspot.co.uk/
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/04/2013 :  12:47:18  Show Profile  Reply with Quote
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.

Cheers
MIK

Edited by - MIK_2008 on 03/04/2013 13:41:11
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/04/2013 :  22:57:37  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 03/05/2013 :  02:32:46  Show Profile  Reply with Quote
Thank you all : visakh16, MIK_2008 ,Transact Charlie
MIK 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--Time
50--------------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 !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 03/05/2013 :  04:00:25  Show Profile  Reply with Quote
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 ordering

If it was a typo, then you can try below

SELECT  *
FROM Table
ORDER BY DATEDIFF(ss,[Order--Time],GETDATE())  ASC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2013 :  05:31:16  Show Profile  Reply with Quote
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 ......? Grrrrrrrrrrr


Cheers
MIK
Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 03/05/2013 :  11:53:08  Show Profile  Reply with Quote
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 !
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2013 :  12:13:11  Show Profile  Reply with Quote
It shows that
1) new record is not the criteria to have a story to be on the top
2) 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


Cheers
MIK
Go to Top of Page

pamyral_279
Posting Yak Master

129 Posts

Posted - 03/05/2013 :  21:22:27  Show Profile  Reply with Quote
Your second approache is good option ! So, can you write procedure or simulation code for temp table which will sort ?

Thank you very much !
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/06/2013 :  04:21:56  Show Profile  Reply with Quote
well attemp yourself and if you face any issue you can ask for help from SQLTeam. thanks!

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000