SQL Commands

[GIDustin]

I have a table in MySQL called Units:

TABLE Units
ID, Owner, Name, Website, ..., AnimRating, SndRating, ...

I want to SELECT from that table sorting by the OverallRating, which is the average of AnimRating and SndRating. Is there an easy way to do this? I guess it would be somehting like:

SELECT * FROM units ORDER BY AVG(AnimRating, SndRating) DESC

Except that doesnt work

Thanks for any help you can provide

- GIDustin

 

[LordKestrel]

I'm not familar with MySQL's specific implementation of SQL, but I don't believe you can order by something that isn't actually a column in the table you're selecting from. You can select an average like you are, but I don't know if you can sort by that.

I'm out of the office for a few weeks, so I don't have any documentation handy, but there is probably a way to do what you want. Have you tried setting up a view that has a column that is the average you want, and then do the select and sort off of that?

 

[GIDustin]

yeah, but then I have to remake the Average column each time one of the others changes. This gets around that. I just want less load for my server.

- GIDustin

 

[kcwong]

How about:

SELECT *, AVG(AnimRating, SndRating) as averageCol FROM units ORDER BY averageCol DESC

*Not tested*... my machine with MySQL is not functional now (no I/O devices and power connected).

 

[Grisu]

hm if I understand it correcty that doesn't work because AVG computes the Average over all selected sets:

how about:

SELECT *, (AnimRating + SndRating)/2 as OverallRating FROM units ORDER BY OverallRating DESC

this should work

?

 

[GIDustin]

By golly that worked. Unfortunately I have already added a "Overall_Rating" column and a subroutine to update the overall rating each time a rating is added.

Thanks for the info though. I may be able to use it elsewhere.

- GIDustin

 

[GIDustin]

Another MySQL Question:

How do you specify a "WHERE" clause that is reverse? Something like:

SELECT * FROM Units WHERE Downloads (IS NOT) 10

So that it gets every unit that does not have a download of 10. I plan to use this on text as well (SELECT .... WHERE Name (IS NOT) 'John') later, so using < and > wont work

- GIDustin

 

[GIDustin]

hmm I guess this seems to work:

SELECT * FROM Units WHERE Downloads != 10

And it works for text as well. NM my post, random ramblings

 

[GIDustin]

New question for you guys. Lets say I have 2 tables:

TABLE Units
ID, owner, name, downloads

TABLE Owners
ID, Name, Email

I want to SELECT from Units sorted by the unit's owner's name. In table Units, owner should match one ID from Owners.

Suggestions?

- GIDustin

 

[kcwong]

What's your specific problem with this one? It is a very simple SQL... very similar to the first one!

TABLE Units
ID, owner, name, downloads

TABLE Owners
ID, Name, Email

I want to SELECT from Units sorted by the unit's owner's name. In table Units, owner should match one ID from Owners.

SELECT
u.*, o.Name
FROM
Units u, Owners o
WHERE
u.ID = o.ID
ORDER BY
o.Name

You should learn how to write these, ask specific problems you've encountered, and post the SQL statements you've tried... instead of asking people to spoon-feed the answer to you.

 

[GIDustin]

First, Thanks for that snippet of code. I do believe it will work for me.

Second, I started coded in MySQL about 3 weeks ago. I am sorry that I dont know how to cross-reference tables yet.

- GIDustin

 

[kcwong]

Then you should find some tutorial on SQL first... learning by experiment could be challenging and fun, but could also take a lot of time for very little advancement. Unless you're expert in learning new things.

I typed "SQL Tutorial" in Google, and found this: http://www.sqlcourse.com/. It's for SQL beginners. Also try http://sqlzoo.net/, a gentle introduction to SQL.

While specific functions vary, the basics are the same. You could use Microsoft SQL Server's online documentation - in MSDN - for a reference to generic SQL.