I have a column in SQL table with a list of items like below: 99987995|M99987995|99987995|. I want to split the items by delimiter(/|) and then count the number of items. for example I will have 3 items from the list i.e 99987995, M99987995, 99987995. Attached is also an image and what the result will look li…
Tag: sql
Why a partitioned table size is larger than a normal table size with the same data
I insert the same data into two tables: one is partitioned, the other is normal. I use the following command to determine the size of the normal table: The output is: 6512 MB I use the following command to determine the size of the partitioned table: The output is: 6712.1 MB The partitioned table has 1001 par…
AWS Athena: How can we get integer value as string with thousand comma separator in AWS Athena
How can we show integer numbers with thousand comma separator. So, by executing the below statement select * from 1234567890 How can we get the result as 1,234,567,890 Answer You can achieve this by casting number to string and using regex: Output: _col0 1,234,567,890 123,456,789 12,345,678 1,234,567 123,456 …
sql – query for all values in table with limit
I have an SQL query which I run in Amazon Athena: where I order by B and take the first row only for the value 1000 for A. However I want to run this query for all values of A in T i.e for each A in T get the first row only and append to the results. How do
Inner join with special conditions
Given an hourly table A with full records, e.g.: And a subset B of it, e.g. I want to keep only those records from A that are in B or at most 2hr behind the B subset, without duplication, e.g. How can the result be achieved with an inner join, without duplication (in this case the hours 8&9) and preservin…
Postgres giving relation does not exist error for alias
Above query is working fine in MS Sql but showing this error when running it in postgres Answer In MS Sql Server it’s possible to update the CTE. In Postgresql you can link to a CTE for an update. For example: However, such update assumes that the id in that table is unique. But, to mark the newest dupl…
How can I change the grammar in mysql?
in my query result However, if you search after changing the trdDtTm format, the results will not come out. How do I fix it? no items.. how to fix it? Answer trdDtTm is a datetime column. compares a datetime with a number hence. This makes hardly sense. Maybe you hope for the DBMS to magically match the two, …
Turn one column into multiple
Im trying to turn a single column of data, id 1 2 3 4 5 6 into: col1 col2 col3 1 2 3 4 5 6 I figured it would be something to do with pivots but I’m still unsure how to accomplish this. Answer We can try using a pivot query with the help of ROW_NUMBER: Demo
Relational database created/stored on client [closed]
Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers. We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations. Closed 5 months ago. Improve this que…
SQL return rows on or after an specific date
I am trying to export a list of member details that have an expiry date after 2019 so I am just after I thought I could simply use the date in a WHERE clause However, it still returns the other entries and not sure why Answer Assuming that you are using SQL Server: dbfiddle