Tag Info

I'm having trouble imagining anything where the data model could legitimately contain 2000 columns in a properly normalised table. My guess is that you're probably doing some sort of "fill in the blanks" denormalised schema, where you're actually storing all different sorts of data in the one table, and instead of breaking the data out into separate tables ...

The latest draft SQL standard that I could find on the internet (dated 21/12/2011) has the following available for use in a query expression: <result offset clause> ::= OFFSET <offset row count> { ROW | ROWS } <fetch first clause> ::= FETCH { FIRST | NEXT } [ <fetch first quantity> ] { ROW | ROWS } { ONLY | WITH TIES }

MySQL 5.0 Column-Count Limits (emphasis added): There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table. The exact limit depends on several interacting factors. Every table (regardless of storage engine) has a maximum row size of 65,535 bytes. Storage engines may place additional constraints on ...

If you don't care which you get, you can use something like this: select max(v.id), max(v.purchasedate), max(v.customerid), v.assetid, max(va.description), max(vb.title) from purchases v, asset va, assetdescription vb where customerid = '$kid' and v.assetid = va.id and vb.assetid = va.id ...

Use correct ANSI group by (not the MySQL abomination extension) and see what happens select sum(score) total,name,gender,dob,country from users join scores on users.id = scores.user_id where date between '2012-01-01' and '2012-01-31 23:59:59' group by name,gender,dob,country having sum(score)>=1000 order by sum(score) desc limit 50 Why? GROUP BY in ...

Things to try: Adding an index on (user_id, date, score) Group by only on scores table and then join to users: SELECT s.total, u.name, u.gender, u.dob, u.country FROM users AS u JOIN ( SELECT user_id, SUM(score) AS total FROM scores WHERE date >= '2012-01-01' AND date < '2012-02-01' GROUP BY user_id HAVING SUM(score) >= 1000 ...

I think some of it goes back to requirements. How much of a name are you storing? Do you ever want to keep the "names" separate (like first, middle, last)? Do you want to handle multiple middle names? I'd say 50 characters should be fairly sufficient for each name if you wished to separate them out. (That will be way more than enough for most names, but ...

It's a measurement system with 2000 sensors Ignore all the comments shouting about normalization - what you are asking for could be sensible database design (in an ideal world) and perfectly well normalized, it is just very unusual, and as pointed out elsewhere RDBMSs are usually simply not designed for this many columns. Although you are not hitting ...

Why would you need to create a table with even 20 columns, let alone 2000 ??? Granted, denormalized data can prevent having to do JOINs to retrieve many columns of data. However, if you have over 10 columns, you should stop and think about what would happen under the hood during data retrieval. If a 2000 column table undergoes SELECT * FROM ... WHERE, you ...

First some more flaming, then a real solution... I mostly agree with the flames already thrown at you. I disagree with key-value normalization. Queries end up being horrible; performance even worse. One 'simple' way to avoid the immediate problem (limitation of number of columns) is to 'vertically partition' the data. Have, say, 5 tables with 400 ...

Here is a good script I shamelessly ripped from here: use [Insert DB Name] select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) , ...

Unfortunately MySQL does not have the handy analytical functions that other SQL languages have. You can simulate them, tough, using some left outer join tricks: SELECT t1.payment_id ,t1.emp_id ,t1.cargeTime ,t1.payment FROM emps t1 LEFT OUTER JOIN emps t2 ON t1.emp_id = t2.emp_id AND ...

While I wouldn't get out of control with what you permit for storage, using a VARCHAR or NVARCHAR type to store the strings makes it a much less critical question - since you're only going to use the storage that your data actually consumes, rather than the full length of your field, you can be a bit more generous here. Given the general pain of changing the ...

If you want to pick the row with the earliest purchasedate as your example seems to indicate, this query may be preferable: SELECT DISTINCT ON (v.assetid) v.id ,v.purchasedate ,v.customerid ,v.assetid ,va.description ,vb.title FROM purchases v JOIN asset va ON va.id = v.assetid JOIN assetdescription vb ON ...