Development FAO: SQL Gurus

I have a table with 8 fields: QID and W1 thru W7
QID = Question ID (Which Question we are talking about)
W1 ~ W7 = Weight 1 thru 7 (the weight of each of the seven options)

I want to write a SQL code that will return two fields, QID, and maxw, where maxw returns the largest value from those 7 rows.

I need to execute the SQL in PHP, and it will be accessing an Access database.

I'm pretty sure Unions won't work with a join, but I'm not sure.


basically the equivalent of php's max($W1, $W2, ~ $W7);

The idea is I need to find out which of the 7 responses for each question is the biggest, so I can determine the maximum score possible for the survey. (I know how to do it with PHP, but in the end I will be totaling up roughly 7~8k questions, and I want to do this as quickly and cleanly as possible)

Restructuring the tables is not an option. (I'm on the bottom of the totem pole right now)

-Thanks

 

Then do it in PHP. Sort the array in descending order and take the zeroth element.

8k = not a lot of records
8M = not a lot of records
8,000M = starting to become a lot of records

 

Thanks, and although I would normally agree that 8k isn't a lot, we are running this on a P3 desktop pretending to be a server, along with about 15 other databases, each ranging from 350~500MB in size, and they are all in MS Access.

So the second lesson I've learned today is you can sometimes save time by simply running 3 queries and making some very strategic multidimensional arrays (foreach is my best friend today )

I say this not because I think I'm smart, but because I'm sure someone will respond with, "Hey, that's a slow way of doing it, try this". Then I will try this, and be a better person for it.
(a man is not simply the sum of his parts, but is in fact the sum of his efficiently running scripts, I'm not much of a man just yet )

 

Hey, that's a slow way of doing it, try running screaming to someplace where they understand what a server is, and what a RDBMS is. (here's a hint)

I picture you getting to work every day and having to make wedge-shaped impressions in clay tablets.

 

Cool! does your work do that too?!

actually we are a fortune 500 company (I think...) with a couple of quad processor servers somewhere in the building, with a collective 500,000GB of storage... but for some reason we aren't allowed on those servers

(they have to run a network of 1200 call center computers, monitoring when people are on the phone with who and for how long, and pretty much whatever they do on their computer... I can even see when they take bathroom breaks... talk about privacy issues!)

 

If you can do it in SQL then you should - that's simple DB optimization.

Something like

Code:

select qid, greatest(w1, w2, w3, w4, w5, w6, w7) as maxw;

 

Yeah, but can Access do that? (Thankfully I don't need to know)

 

The good news is I brought an old shoebox from home, which may serve as a better alternative to using access for data storage.

But oh well, we are going for functionality, not efficiency. (we are hoping our parent company will realize how great this system is, then they will convert it to ASP and run it on an oracle database)

I swear if you pound a square peg long enough it is bound to bend and break enough to fit in this round hole...

 

There ya go. Get some 4x6 index cards for that shoebox and you've got a real data warehouse.

 

can Access - get a real DB. MySQL when optimized is fast. (If you need help optimizing MySQL, I'll help you.)

 

They are big databases man! Start thinking of an upgrade. MS Access wasn't made for that amount of data. Or try repairing them.

 

And remember, MS Access doesn't support SQL! At least that's what MS will tell you as their way of getting around having to fix bugs and make it half-useable.