This seemed like a very easy thing to do but I got stuck. I have a query like this: select op.date, count(p.numberofoutstanding) from people p left join outstandingpunches op on p.fullname = op….
Tag: sql
SQL get value from XML in tag, by tag value
I have the following XML: TEST1 D028 …
A query for getting results separated by a date gap
ID TIMESTAMP 1 2020-01-01 12:00:00 2 2020-02-01 12:00:00 3 2020-05-01 12:00:00 4 2020-06-01 12:00:00 5 2020-07-01 12:00:00 I am looking for a way to get records in a MySQL database that are …
PostgreSQL add new not null column and fill with ids from insert statement
I´ve got 2 tables. The tables are already filled with a lot of data. Now I want to add a new column content_id (NOT NULL) to the data table. It should be a foreign key to the content table. Is it possible to automatically create an entry in the content table to set a content_id in the data table. For
Daily active counts of THING from rows detailing change of THING
Say I have a table of people who have used my service on a day N and a table describing what theme the users have changed to. There’s no table available telling me what theme they are currently using. …
Join table with mapping of array in postgres
I want to join two table, with first table contain an array which is primary keys of second table in JSON format Answer demo:db<>fiddle You can use the ANY function: Edit: demo:db<>fiddle If you have jsonb arrays, you can use the @> operator. Note, that this works only if your integer id values…
Consolidate union result
I am selecting email address from two source tables via a union query. Normally, I would leverage union’s behavior of filtering out duplicate values, but in this case, each source table may or may not have a value for each person, and I have to give “priority” to the email address from the f…
Inner Join returned values
I have a query such as, select name from employee inner join task on employee.id = task.employee_id order by name asc Tables look like: employee id name 1 Emily 2 Sam 3 AI 4 Joe 5 …
How to select TOP records in Postgres. Not a similar question
I have a table with three columns (City, Orderid, Total_quantity) and more than 500 rows. I want to fetch the top 50 records according to total_quantity wise. I can do this with rank_number but the …
fetch records based on mysql alias using having clause
i am having bibles table where user read 3 bible chapters every day. My query works fine as from this answer . i have two columns in table id, chapter_name i.e below :- and after that i used below query Its giving me blow output :- what i want when user requests for date 24 feb 2020 it must come