Tag Info

Hot answers tagged

18

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 ...


10

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 ...


9

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 }


6

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 ...


6

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 ...


5

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 ...


5

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 ...


4

The size of the database is the size of the file. Look at the actual size of the data file (the transaction log doesn't count). Yes indexes count. If you are running out of space consider an upgrade to SQL 2012 Express as that increases the size limit to 10 Gigs.


4

In SQL Server and other systems that support ROW_NUMBER()... WITH GoalsWithGoalNum ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY [In] ORDER BY [Goal ID] ) AS GoalNum FROM Goals ) SELECT * FROM GoalsWithGoalNum WHERE GoalNum <= 2;


3

What is wrong with the (maybe too obvious?): select * from noun n, noun_inflection ni where n.label = 'handlebar' and n.label ilike '%'||ni.label_singular order by char_length(ni.label_singular) DESC limit 1;


3

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 ...


3

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 ...


3

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 ...


2

Table have 1,000,000 records but It look like Table doesn't have 800000+ records where `articles`.`hash` NOT IN ( '1z8y' ) But table have 2000+ records where `articles`.`hash` NOT IN ( '1z8y' ) LIMIT 800000,10 will return 10 records after 800000th records but it looks like you didn't have 800000 records which satisfy your where clause condition. You ...


2

Ok, it was simple :) I wanted to limit each database to 256MB of space per student. In order to achieve that, I enabled smallfiles in /etc/mongod.conf and ran the mongod process with mongod -f /etc/mongod.conf --quota --quotaFiles 4 which limited the size of the database to 256MB. smallfiles starts preallocating space with 16MB, then 32MB, 64MB and finally ...


2

In terms of operation SELECT id,name,description FROM tablename LIMIT 1000,25 SELECT id,name,description FROM tablename LIMIT 25 OFFSET 1000 there is absolutely no difference in the statements @siride's comment is exactly the point. LIMIT 1000,25 means LIMIT 25 OFFSET 1000 From the same Documentation LIMIT row_count is equivalent to LIMIT 0, ...


1

I have the same issue. Mongo does not respect quota. I haven't found the answer, but I found SERVER-5136 . According to this, it is recorded issue but sadly resolution is still not planned.


1

When sp_spaceused shows the database_size of about 25 GB in your first result set, that includes the data file/indexes, and the transaction log. Only the actual data and indexes count towards the SQL Server Express limits, so you have about 3.3 GB of data (as in your second result set) and the remaining 22 GB will be the transaction log. If you run ...


1

You can separate the problem into multiple steps. First lets create an example table: SQL Fiddle MySQL 5.5.32 Schema Setup: CREATE TABLE Visitors( id INT, -- other columns country CHAR(2) ); INSERT INTO Visitors(id,country)VALUES(1,'DE'); INSERT INTO Visitors(id,country)VALUES(2,'DE'); INSERT INTO Visitors(id,country)VALUES(3,'DE'); INSERT INTO ...


1

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 ...


1

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)) , ...


1

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 ...


1

Try: SELECT * FROM yourtable ORDER BY chargeTime DESC LIMIT 3


1

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 ...



Only top voted, non community-wiki answers of a minimum length are eligible