sql

[Solved] How to select specific data between Quotes (“)

by

this is Ugly, but will eventually work: COLUMN = ‘jksjdksls#$#$@@kskjfjf,”123,456,789″ lsnslkdswfnslsjfls’ left( right(COLUMN,len(COLUMN)-instr(COLUMN,””””)), instr( right(COLUMN,len(COLUMN)-instr(COLUMN,””””)), “”””) -1 ) –> 123,456,789 This is what is done: We take this string ‘jksjdksls#$#$@@kskjfjf,”123,456,789″ lsnslkdswfnslsjfls’ find the first occurence of ” with instr(COLUMN,””””) –> returns 24 take the right end of the string with. Therefore we need to take … Read more

[Solved] How to convert varchar time to 24hours Time in SQL Server [closed]

by

Not clear whether you want your result as a VARCHAR or an actual TIME type, so here’s both: DECLARE @Table TABLE ( TXN_TIME VARCHAR(6) ) INSERT INTO @Table SELECT ‘053124’ UNION SELECT ‘173932’ UNION SELECT ‘011815’ UNION SELECT ‘120349’ UNION SELECT ‘134207’ SELECT TXN_TIME, LEFT(TXN_TIME,2) + ‘:’ + SUBSTRING(TXN_TIME,3,2) + ‘:’ + SUBSTRING(TXN_TIME,5,2) + ‘:000000’ … Read more

[Solved] PL/SQL Implementation of Hungarian/Kuhn-Munkres Algorithm [closed]

by

I couldn’t find one…so I made one. Now I want to share it with anyone else who needs it. It has been tested and validated, and any additional comments are welcome. https://github.com/manas1213/hungarian_algorithm This is based on the comprehensive algorithm outlined at http://csclab.murraystate.edu/bob.pilgrim/445/munkres.html. solved PL/SQL Implementation of Hungarian/Kuhn-Munkres Algorithm [closed]

[Solved] How to get the sum in a joined table when using group by – getting wrong results

by

I understand that you want, for each day: the sum of order_items.energy_used for all orders created that day the created_at and order_sum that correspond to the latest order created on that day Your sample data is not very representative of that – it has only one order per day. Also it is unclear why created_at … Read more

[Solved] ORA-00907: Missing right parenthesis [closed]

by

CREATE TABLE s( id int, y int NOT NULL UNIQUE, x int NOT NULL, z varchar(50) NOT NULL, k varchar(50), l varchar(50) NOT NULL, m int NOT NULL, v int NOT NULL, c int NOT NULL, r varchar(400), CONSTRAINT s_pk PRIMARY KEY (id)); 2 solved ORA-00907: Missing right parenthesis [closed]

[Solved] I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]

by

Actual code depends on tool you use. In SQL*Plus, it is the &; in SQL Developer or TOAD, that would be a :. Also, GUI tools usually don’t require enclosing string parameters into single quotes. Here’s a SQL*Plus example: SQL> select * from emp where job = ‘&job’ and sal <= &sal; Enter value for … Read more

[Solved] mySQL Largest number by group

by

In general ORDER BY in a sub-query makes no sense. (It only does when combined with FETCH FIRST/LIMIT/TOP etc.) The solution is to use a correlated sub-query to find the heaviest fish for the “main query”‘s current row’s username, location, species combination. If it’s a tie, both rows will be returned. SELECT * FROM entries … Read more

[Solved] most efficient way to write this sql query?

by

This is a little more difficult because MySQL doesn’t have Row_Number() so you’ll need to simulate it. One way is use a self join and a count. You could also use the @rownumber technique described here In my example I used animal_id to find the most recent but you may want to change the JOIN … Read more

[Solved] MySQL/SQL query with errors [closed]

by

Use this way, UPDATE shopproducts AS wesp JOIN currencies AS wec JOIN shopcategories AS wesc JOIN shops AS wes SET wesp.totalprice = case when (wesc.adelivery = 1) then ROUND(wesp.price / 100 * wec.value, 0) + wes.adelivery …..(like this way all the when) end WHERE wesp.currency = wec.name AND wesp.sortcategory = wesc.category AND wesp.shop = wes.name … Read more

[Solved] Need a SQL Server query to eliminate the Highlighted Rows ( Returning Routes in Flight)

by

I have created a table named S65828793 with your provided data. First I have numbered the rows in ascending sequence of departure time. Then from that I have identified the flights that have been another flight within two consecutive days from opposite direction and marked those as returning flight. Then at last I have excluded … Read more

[Solved] PHP sql query isn’t returning anything

by

From the manual: Identifiers may begin with a digit but unless quoted may not consist solely of digits. http://dev.mysql.com/doc/refman/5.1/en/identifiers.html Which is exactly what you’re doing, and should not be doing. Solution: Choose a different name for your table, one consisting of letters preferably. Use backticks around the table name. Plus, if you wish to view … Read more