how to get the full resultset from SSMS
I cast it to XML select @variable_with_long_text as [processing-instruction(x)] FOR XML PATH The processing-instruction bit is there to stop it entitising characters such as < to <
sql
Is there a postgres CLOSEST operator?
I may be a little off on the syntax, but this parameterized query (all the ? take the ‘1’ of the original question) should run fast, basically 2 B-Tree lookups [assuming number is indexed]. SELECT * FROM ( (SELECT id, number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above UNION … Read more
Select random row from a PostgreSQL table with weighted row probabilities
This should do the trick: WITH CTE AS ( SELECT random() * (SELECT SUM(percent) FROM YOUR_TABLE) R ) SELECT * FROM ( SELECT id, SUM(percent) OVER (ORDER BY id) S, R FROM YOUR_TABLE CROSS JOIN CTE ) Q WHERE S >= R ORDER BY id LIMIT 1; The sub-query Q gives the following result: 1 … Read more
Escaping ‘ in Access SQL
The “Replace” function should do the trick. Based on your code above: DLookup(“island”, “villages”, “village=”” & Replace(txtVillage, “””, “””) & “‘”)
How to get the byte size of resultset in an SQL query?
select sum(row_size) from ( select char_length(column1)+ char_length(column2)+ char_length(column3)+ char_length(column4) … <– repeat for all columns as row_size from your_table ) as tbl1; char_length for enum, set might not accurate, please take note
How can I use SUM() OVER()
Seems like you expected the query to return running totals, but it must have given you the same values for both partitions of AccountID. To obtain running totals with SUM() OVER (), you need to add an ORDER BY sub-clause after PARTITION BY …, like this: SUM(Quantity) OVER (PARTITION BY AccountID ORDER BY ID) But … Read more
How to insert Arabic characters into SQL database?
For the field to be able to store unicode characters, you have to use the type nvarchar (or other similar like ntext, nchar). To insert the unicode characters in the database you have to send the text as unicode by using a parameter type like nvarchar / SqlDbType.NVarChar. (For completeness: if you are creating SQL … Read more
Check if a time is between two times (time DataType)
I suspect you want to check that it’s after 11pm or before 7am: select * from MyTable where CAST(Created as time) >= ’23:00:00′ or CAST(Created as time) < ’07:00:00′
Get the first and last date of next month in MySQL
Use: SELECT DATE_SUB( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ), INTERVAL DAY( LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) ) )-1 DAY ) AS firstOfNextMonth, LAST_DAY( DATE_ADD(NOW(), INTERVAL 1 MONTH) )AS lastOfNextMonth
Insert into from CTE
This is the syntax to insert into a table from a CTE: — CREATE TABLE tmp ( tmp_id NUMBER(10) ); INSERT INTO tmp( tmp_id ) WITH cte AS ( SELECT 1 AS tmp_id FROM dual ) SELECT tmp_id FROM cte;