Inspired by the thread about most expensive Spectrum software:
viewtopic.php?f=21&t=1463
I tried to search such releases directly in ZXDB database.
That's what I came with:
SELECT R.release_price, E.*
FROM entries E
INNER JOIN releases R ON E.id=R.entry_id
ORDER BY R.release_price DESC
The problem is that this sorting doesn't really work as supposed. Release_price in the database is not a number, it's a string. So I get list like this:
The order is wrong, also the column contains values in different currencies so it doesn't make sense to compare pounds to pesetas.
The proper way from database point of view, I guess, would be to hold pure numbers in one column and currency_id in another column
Is there any better way to get games sorted by a price?
[mention]Einar Saukas[/mention] I recall you did some work on ZXDB prices. Do you have any future plans about it?
Prices in ZXDB
Moderator: druellan
Re: Prices in ZXDB
You could use 'left' to get the currency, then the following to convert the remainder of the field to a numeric:
https://stackoverflow.com/questions/596 ... ysql-query
https://stackoverflow.com/questions/596 ... ysql-query
Re: Prices in ZXDB
It doesn't even make sense to compare pounds to pounds. Inflation was much higher in the 1980's - full price and budget title prices both doubled from 1983-93 - so any comparison is meaningless unless it's over a very short term.
- Einar Saukas
- Bugaboo
- Posts: 3118
- Joined: Wed Nov 15, 2017 2:48 pm
Re: Prices in ZXDB
... and also additional information (standard or deluxe case, etc) in a third column.
The problem is, this information came from Martijn's WoS, that stores multiple prices in the same field. For instance:
https://spectrumcomputing.co.uk/index.p ... 6&id=11043
https://spectrumcomputing.co.uk/index.p ... id=1000456
Here's a query to obtain highest prices in pounds:
Code: Select all
select * from (select
replace(substring_index(release_price,' ',1),'£','') as price,r.*,e.*
from entries e inner join releases r on e.id = r.entry_id) as x
where release_price like '£%' order by price*1 desc;
A future improvement I'm planning for ZXDB, is to categorize multiple "editions" (or "versions") within each release. For instance, if a certain game was originally released by Ocean and later re-released by Erbe, it's already stored in ZXDB as 2 different releases. However if Erbe sold both standard and deluxe versions, there's currently no distinction in ZXDB. This could be changed, so they would be stored in ZXDB as 2 separate "editions" of the same release (each one with its own price, release date, tape images, inlays, etc). This way, we will have a single price per edition, thus making it possible to split price parts into separate columns as you suggested.
Supporting both "releases" and "editions" in ZXDB would help further integration with SPA2 and ZX-Art. Both these databases currently categorize "editions" instead of "releases".
However this change will take considerable effort. It must be planned carefully and coordinated with other sites that depend on ZXDB, so it won't break anything. But this will have to wait for later. Right now, I'm prioritizing other areas that won't take so much work and provide more visible benefit for end-users, for instance improving data association with magazines.
Re: Prices in ZXDB
Fair said, Einar!
Now when the structure of old WOS database became public we can all see that it's certainly not perfect and allows for a lot of improvement. It's great that you took the job as otherwise we would have just old, broken website not updated for years.
And I agree totally that things "visible" and annoying to user should have priority. Prices stored as they currently are were used for years and 99% of time nobody complained about them.
So do things in your order. I have a feeling that you'll be doing it right
Now when the structure of old WOS database became public we can all see that it's certainly not perfect and allows for a lot of improvement. It's great that you took the job as otherwise we would have just old, broken website not updated for years.
And I agree totally that things "visible" and annoying to user should have priority. Prices stored as they currently are were used for years and 99% of time nobody complained about them.
So do things in your order. I have a feeling that you'll be doing it right