473,320 Members | 1,848 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MySQL Sorting Question

Hello everyone ! ,
I am working on a rating field in my database. Basically , my rating formula goes like this
Expand|Select|Wrap|Line Numbers
  1. RATING = TOTAL POINTS / TOTAL RATINGS
Ofcourse , its the average rating. I store the total points and total ratings in a single field called 'catrating' (like 4/2 i.e points=4 ratings=2) and explode it with php to get required values. What am looking for is , is there an easy MySQL inbuilt function to sort the ratings ( Like sorting numbers) like these directly in the query with some REGEX and AVG , so that all the top rated stuff gets sorted in the DESC order ?

Thanks so much !
Oct 20 '08 #1
4 1209
nathj
938 Expert 512MB
Hi,

Why not store the two values in separate fields?

This would then enable you to more accurately use the MySQL 'ORDER BY' clause.

The clause is simple enough ORDER BY tbl.field ASC/DESC. you can even tack orders together thus ordering on multiple columns.

I would have though this was the easiest way to go.

I admit this may involve a change in your existing code but it's going to make this job easier and give you greater flexibility in the future.

If you have a good, technical reason for not doing this then post it here as I can think of one - other than potential headache of updating existing code, and that may not be an issue for you depending on how much code there is.

Cheers
nathj
Oct 20 '08 #2
Atli
5,058 Expert 4TB
I would agree with nathj.

Storing two values in a single field should never be done. It goes against one of the most basic *rules* of relational database design. It does nothing but cause problems (as you are experiencing).

The best way to do this would be to separate the two values and have MySQL sort the values using ORDER BY.
Oct 20 '08 #3
ok ty guys , i'll make 2 fields
Oct 21 '08 #4
nathj
938 Expert 512MB
ok ty guys , i'll make 2 fields
Smart decision.

If you get stuck or need any further help then just give us a shout.

Cheers
nathj
Oct 21 '08 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Roger Lord | last post by:
From a html file I have something like this: <a href="showtable.php?table=clients&col=lastname"><b>Mysql Table</b></a> This is a general mysql table viewer for a database. showtable.php...
7
by: Jim | last post by:
I'm using PHP & MySQL to create a simple guestbook. I've created my table and I'm able to load my information in as usual. I would like it to display the latest entry first though. I set an id...
0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
2
by: mos | last post by:
I want to put a MySQL 4.1 database on a Win2k laptop but the problem is it contains confidential client information. It has to be Window because applications accessing the database are written in...
0
by: Russ | last post by:
Hi, I'm doing some web development and have mysql (3.23.x) installed to test with (win2k, but I don't think this question is particularly platform specific). I have a reasonably complex query...
0
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...
22
by: mike | last post by:
If I had a date in the format "01-Jan-05" it does not sort properly with my sort routine: function compareDate(a,b) { var date_a = new Date(a); var date_b = new Date(b); if (date_a < date_b)...
3
by: Neeper | last post by:
I'm creating an application for multiple cities (about 20-50 cities). I'm not sure whether to use a single table to store for all cities' items or break each one out into a seperate table for each...
6
by: =?Utf-8?B?RGFu?= | last post by:
I am reposting a question from about 3 weeks ago ("sorting capability"). I have an aspx page in which I get the data from a database dynamically, through C# code, by creating a dynamic table...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.