Tag Info

New answers tagged

0

There are a couple of ways you could separate the current values from the historic ones: you could simply include a boolean field that is true for the latest price and filter on that, or you could keep current prices in a separate table. Both options involve a little extra work to maintain integrity but would make a query for current prices more efficient. ...


0

I think you're looking at something like this: Select A.Max_id, harddrives.id As id1, harddrives.serial_number, brands.name, harddrives.size, location.name As name1, encryption_type.name As name2, resource.name As name3, backups.start_time, backups.end_time FROM ( Select backups.resource_id, Max(backups.id) As Max_id FROM ...


0

harddrives.id As id1 Update it to (Max)harddrives.id As id1 I think that should work.


2

If Shanooooon's suggestion is not enough, then SELECT (stuff from x, plus picture stuff) FROM ( SELECT ... (everything except `picture` stuff) LIMIT 30 ) x LEFT JOIN pictures ON x.picture_id = pictures.id This helps because it will reach into pictures only 30 times, not 1000 times.


2

Instead of using a sub query you could use a LEFT JOIN. That way you negate the need for a sub query which is being looked at for each image. LEFT JOIN seen ON seen.image_id = images.id AND seen.user_id = $user_id Then only get rows that haven't been joined. WHERE seen.id IS NULL


1

You need to use the explain facilities in DB2 to answer this question, it's highly specific to the actual queries you are executing, and you can't get a general answer. IBM i 7.2 (and previous versions) have Visual Explain. If you are restricted from doing this, then you need to speak to the system or database administrator for your environment.


0

If you want random rows per parent_id, your inner ORDER BY should arrange rows in a way that same parent_id rows are grouped together. So, instead of ORDER BY rand() it should be ORDER BY parent_id ASC, rand() As for this: it returns to me Ascending id's each time I try The reason your query returns ascending IDs each time is because you have ...


2

Explanation By definition in the SQL standard (which Postgres implements) you can reference output columns in ORDER BY or GROUP BY, but not in the WHERE or HAVING clause. The documentation: An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write ...


1

Preamble: Check out Erwin's answer on this for a really interesting and detailed explanation. I think one way to get this to work appropriately is to name the subquery via a CTE, like WITH cats AS(SELECT category_id FROM category_schedule_con con WHERE s.id = con.schedule_id ORDER BY category_id)... and then later when you need to apply your WHERE ...


0

You can split the query by each <table>.lastmodifieddate > '2015-07-01' and then use UNION to run them all at once. Regarding some comments about indexing - indexes are crucial to fast query execution, it is not about complexity of a table but about number of rows - small table can be easily kept in memory but large one cannot so index can help to ...


0

I suggest you to try with the APPLY join operator. It is useful when you have to join tables where one table has much more rows than the other tables. it's taking an eternity because the large size of the resultsets Which indexes are involved on the execution plan of the query? Can you post the execution plan used for the original query? Thanks


2

You didn't specify which database you were using, but in most databases, you can use a CASE expression inside the COUNT aggregate function to accomplish your goal: select FORMAT(fecha, 'yyyy-MM') as FECHAS, COUNT(*) as TOTAL, COUNT(CASE WHEN tipo = 'C' THEN 'X' END) as CREDITO, COUNT(CASE WHEN tipo = 'D' THEN 'X' END) as DEBITO from ...



Top 50 recent answers are included