Update database item
(1) By Birdy (SQLBirdy) on 2021-02-21 12:02:51 [link] [source]
Hello, I am using Windows and SQLite Expert. I have a SQLite database with items like Title, Artist, Ranking, Points, HP (highest position), WK (number of weeks) and some other items from the dutch Top40 Chart. Every week the new list is published on internet and I add the new list to the database. After inserting the new list I reorder the database with SELECT Ranking, Points, HP, Wk, Artist, Title FROM Top40 ORDER BY Points DESC, Hp ASC, Wk DESC The result looks like this: Ranking Points Hp Wk Artist Title 221 7 7 Justin Bieber Anyone 215 10 8 Ed Sheeran Afterglow 200 1 5 Olivia Rodrigo Drivers License 194 3 6 The Kid Laroi Without You 178 11 8 Hvme Goosebumps After reordering I want to update the Item Ranking with the sorting order. So Justin Bieber will be 1, Ed Sheeran will we 2 etc. The result woud be: Ranking Points Hp Wk Artist Title 1 221 7 7 Justin Bieber Anyone 2 215 10 8 Ed Sheeran Afterglow 3 200 1 5 Olivia Rodrigo Drivers License 4 194 3 6 The Kid Laroi Without You 5 178 11 8 Hvme Goosebumps What is the right syntax in SQLite to update my database with Ranking filled with the correct order. I have searched on internet and found the following instruction. SELECT Ranking,Points,Artist,Title, RANK () OVER (ORDER BY Points desc, HP asc, Wk desc) SortOrder FROM Top40 Ranking Points Hp Wk Artist Title SortOrder null 221 7 7 Justin Bieber Anyone 1 null 215 10 8 Ed Sheeran Afterglow 2 null 200 1 5 Olivia Rodrigo Drivers License 3 null 194 3 6 The Kid Laroi Without You 4 null 178 11 8 Hvme Goosebumps 5 The SELECT instruction works but with a new virtual column SortOrder. I want to use the item Ranking instead. Can I use such an instruction to update Ranking?
(2) By Larry Brasfield (larrybr) on 2021-02-21 14:42:41 in reply to 1 [link] [source]
After inserting the new list I reorder the database with SELECT ... ORDER BY Points DESC, Hp ASC, Wk DESC
More accurately, you get an ordered query result; the database is unchanged.
Without condoning the practice of back-filling transient data into a table, I can say that this example (of such practice) may be of interest:
-- Setup example table and content.
CREATE TABLE DogJumps (name TEXT, jumpsOverLastWeek INTEGER, recentRank INTEGER);
INSERT INTO DogJumps (name, jumpsOverLastWeek)
VALUES ('Fido', 42), ('Fleaser', 1), ('Bowser', 21);
-- Define a view on DogJumps with ranking as of view time.
-- The stored recentRank is ignored; it may not be current.
CREATE VIEW TopDogsNow AS SELECT name, row_number() OVER rankNow AS ranking
FROM DogJumps WINDOW rankNow AS (ORDER BY jumpsOverLastWeek DESC)
ORDER BY name;
-- Place current ranking in DogJumps as recentRank.
UPDATE DogJumps SET recentRank = tdn.ranking
FROM (SELECT ranking, name FROM TopDogsNow) AS tdn
WHERE tdn.name = DogJumps.name;
You can lookup WINDOW functions in the SQLite docs to see how that works.
(3) By Ryan Smith (cuz) on 2021-02-21 15:35:03 in reply to 1 [link] [source]
This is an enormous sin in Database terms.
Why back-fill the data with information that is essentially a simple calculation on existing data?
The ranking is true and can be deduced of any current set of information, there is no need to "store" it in the data, yet there is a significant inset cost to achieve this, especially in development time as you have now experienced.
This will more than be adequate for the purposes, unless there is some specific thing you neglected to tell us:
CREATE VIEW Top40Ranked AS SELECT RANK () OVER (ORDER BY Points desc, HP asc, Wk desc) AS Ranking, Points,Hp,Wk,Artist,Title FROM Top40 ; Then, if you select all from that view (or any subsection of the fields), like this: SELECT * FROM Top40Ranked ORDER BY Points desc, HP asc, Wk desc; You should get this: Ranking Points Hp Wk Artist Title 1 221 7 7 Justin Bieber Anyone 2 215 10 8 Ed Sheeran Afterglow 3 200 1 5 Olivia Rodrigo Drivers License 4 194 3 6 The Kid Laroi Without You 5 178 11 8 Hvme Goosebumps
And best of all, when you next add new data, this view will automagically again show the correct values. PS - Even if you don't include the ORDER BY clause in the last query, the Ranks will still be correct even if the rows might be in a different order.
Does that not solve your quest exactly?
(4) By Larry Brasfield (larrybr) on 2021-02-21 15:53:53 in reply to 3 [link] [source]
This is an enormous sin ... Why back-fill the data with information that is essentially a simple calculation on existing data?
Ryan, I do not disagree with eschewing such practice. However, we cannot be certain from the OP's original post whether he/she (or possibly it's a bird) actually intends to engage in that practice. For all we can tell, without surmise as to what her/his/its data means and what result is desired, the intention is to record, in the same table, both transient data affecting ranking and a week-by-week rank which changes only upon weekly update. IOW, if that is the intention, your always-current view gives the wrong answer.
That is why I elected to answer the question posed, even though I suspected (but did not know) that such answer was a solution to a mis-framed problem.
(5) By Ryan Smith (cuz) on 2021-02-21 16:16:03 in reply to 4 [source]
While I can agree with the idea that someone (of human or ornithological descent) with different intentions might end up doing something different than back-filling data, I have to say that, to my mind, the OP's first two paragraphs leaves nothing to the imagination.
I also absolutely agree that one should, in addition to any eschewing or alternate suggestion, answer the original question - but your post had already covered that grounds perfectly and I couldn't think of anything to add to it.
I guess what we should do is make it clear to the OP: Sir/Ma'am/Gentle Fowl, what you've asked CAN be achieved (see Larry's solution), but, if your description of what you need it for is precise, shouldn't be done (see Ryan's post for an alternate method).
Either way, best of luck and do ask if anything is unclear or any other questions arise.
(6) By TripeHound on 2021-02-21 16:35:32 in reply to 5 [link] [source]
Should any descendants of clade Amniotes1 actually want to store the week-by-week rankings over time, they probably should be in a different table that looks something like
WeekNo Ranking ArtistID 1 1 23 (-> Justin Bieber in an "Artists" table) 1 2 32 (-> Ed Sheeran) ... ... ... 1 40 ?? 2 1 32 (Ed Sheeran knowns Justin Bieber of the top spot, pop-pickers) etc.
1 According to A Bird’s-Eye View of Human Language and Evolution, the last common ancestor of birds and mammals (the clade Amniotes) lived about 310 – 330 million years ago.
(8) By Ryan Smith (cuz) on 2021-02-21 20:10:53 in reply to 6 [link] [source]
Going to be on my sign at the next "Save the pigeon" rally: "Birds are Amniotes too!"
Might even be reusable at a rally against sexism.
(7) By Larry Brasfield (larrybr) on 2021-02-21 18:22:12 in reply to 5 [link] [source]
To be clear, I think your lesson was probably applicable and needed. I was mainly responding to "This is an enormous sin ...". In my consulting work, my more significant added value often began with "I don't think that's the problem which needs to be solved here.", followed by stating a different problem whose solution would better serve the client. Often, that view prevailed. But sometimes there were good reasonsa that a more comprehensive solution was not the best. Exploration of those issues is usually worthwhile and well received if sufficiently brief.
Of course, being hopeful of a continued (and remunerative) relationship, I would not speak of sin or offense against principles known among the more learned.b I suppose being so direct is more workable in a forum like this. After all, we can hardly be fired.
a. A common reason to prefer an ugly solution is that it better fits into an already-ugly larger design, and a fix is needed soonest.
b. Clients can be touchy, particularly when invested in a perspective that is not working. I see tact as serving their real need.
(10) By Birdy (SQLBirdy) on 2021-02-22 19:35:17 in reply to 3 [link] [source]
First of all, thank you for all the answers and yes the message is loud and clear. I know that it is not clever to back-fill the data but that was my first attempt to get my problem solved. I have just started using SQLite for my database and I still have to learn a lot. SQLite is the right software for me because it is just a single standalone database and it is freeware. I am very satisfied with the option from Ryan. It is exactly what I was looking for. Thank you for pointing me to the right direction. Answer for John: there are some more fields in the database like Country and First charted but for my question it was not relevant.
(9) By John Gelm (gelmjw) on 2021-02-22 15:53:46 in reply to 1 [link] [source]
Why is there no column for "week the new list is published"?