New answers tagged select
0
Please observe the comments on your question.
Here is an ANSI SQL way to achieve what you're looking for:
SELECT MAX(customer.[NAME]) AS [Name]
FROM customer
INNER JOIN account
ON customer.id = account.[customer.id]
GROUP BY customer.id
HAVING COUNT(*) > 1
This joins the customer and account table together on the customer id (note it's bad practice ...
0
After some digging, I managed to successfully copy history and trends from one item to another.
The sort of syntax I used is this:
History table:
INSERT INTO history (`itemid`, `clock`, `value`, `ns`)
SELECT '<new_itemid>', `clock`, `value`, `ns`
FROM history
WHERE itemid = <old_itemid>;
Trends table:
INSERT INTO trends (`itemid`, `clock`, `num`,...
1
You have an NULL value in your table, so your last query for given row is like this:
Select * from EMP WHERE NULL = 0.
Where NULL is the value in the table and the 0 is returned as the minsalary value.
Correct would be:
Select * from EMP
where coalesce(SAL,0) = coalesce((select min(SAL) from EMP),0)
3
So why isn't MCQUEEN among PORTMAN and RENO employees with zero
salary?
The issue is this predicate:
WHERE SAL = (SELECT MIN(ISNULL(SAL, 0)) FROM EMP);
At run time, the above comparison for the MCQUEEN row with NULL salary is:
WHERE NULL = 0;
The result of any comparison to NULL is UNKNOWN per the ISO SQL standard. The UNKNOWN result is neither TRUE nor ...
1
Test
select count(*)
from person p
cross join task t
where EXISTS (
select NULL
from task_participant tp
where tp.person_id = p.id
AND t.id = tp.task_id )
and p.id = 1234
1
You can use something like the following which will list every table (with their row counts) for every schema in the database:
select n.nspname as table_schema,
c.relname as table_name,
c.reltuples as rows
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
where c.relkind = 'r'
and n.nspname not in ('information_schema','...
3
There is no real improvement possible, but I think the following is more readable:
SELECT datname
FROM pg_database
WHERE NOT datistemplate
AND datallowconn
AND datname <> 'postgres';
3
Test 1 is logically incorrect for this use-case. If you provide NULL as the value of :B1, it will return 0 rows.
Test 2 is logically correct, but its execution plan is suboptimal, so is performance. In this case the optimizer is unable to utilize the index, because NULL values are not indexed by default.
Test 3 is logically correct, and its execution plan is ...
0
If you have a fixed number - say n - of levels in your tree, you can use n-1 self-joins and then aggregate over that. However, most of the time the depth of the tree is not fixed so you need a variable number of joins.
A recursive CTE can be thought of a just that, a variable number of joins. The syntax admittedly is a lot worse than in other languages, and ...
2
I think if you get your head round a basic recursive CTE implementation, you won't find them so difficult to understand in future.
I'm going to try and show you how to do the COUNT(*) you mention in very, very easy steps.
We are going to find all Animals that are of the Cat family. Apologies to any taxonomists here.
1. Firstly, we need to define a starting ...
0
It sounds like you're designing and thinking about the problem more procedurally as opposed to relationally. You should normalize your data so that the top level category Animals is it's own table with Dogs and Cats being values in the Animals table. Then attributes of those Animals would either be additional fields in the Animals table like AnimalColor, OR ...
0
Since the column type is a datetime and this is SQL Server 2016, converting it to a date is easy :
SELECT CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated]) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY CONVERT(Date,[SCAData].[dbo].[Camera].[DTUpdated])
ORDER BY 1;
If you really want to do it via an VARCHAR field,...
0
It is unclear which of two problems you are asking about. I will address both.
The question of missing days requires another table:
SELECT ...
FROM table_of_days AS d -- pre-construct a big table of lots of days
LEFT JOIN your_table USING(dy)
WHERE d.dy BETWEEN ... AND ... -- the range of days desired for this SELECT
(This is one ...
0
Tadaa, and may i be forgiven :D
create table alreadyReturned(id int primary key)
create or alter function fn_a (@id int)
returns int
as
begin
declare @sql varchar(4000),
@cmd varchar(4000),
@retval tinyint
if not exists (select 1 from alreadyReturned where id = @id)
begin
select @sql = 'insert into ...
1
I have deleted my first answer (see fiddle here) 'cos it got me a -1 from some shpxre(*)! Anyway, try, try, try again!...
You appear to want "something for nothing" in that you want the system to do what is not normally possible via the "normal" RDBMS's, but you want this to be automatic. Unless there is a seismic shift in the relational ...
0
I believe that the only thing that can filter silently the output of a select done on a flat table is Row level Security:
https://docs.microsoft.com/en-us/sql/relational-databases/security/row-level-security?view=sql-server-ver15
The challenge is to capture the IDs already selected. Maybe with a security audit https://docs.microsoft.com/en-us/sql/relational-...
0
Without the ability to modify the table, or using a WHERE clause to query it, then you don't have many options other than using one of the features of SQL Server that are applicable for revision control:
Change Data Capture (CDC) - Change data capture records insert, update, and delete activity that applies to a SQL Server table. This makes the details of ...
0
Change Data Capture is specifically designed for this. It adds further tables to the DB, and other meta-data items. The consumer has to track what changes it has seen up to.
I'm using it in my current project to pull changes from the OLTP system into the data warehouse.
1
Consider creating this composite index,
CREATE INDEX some_table_indx_col3_col2 ON some_table(col3,col2);
You should remove the col3 index to avoid index redundancy and conserve storage space.
Considering these existing details,
Query_cache_size 2,556,593,152
less Qcache_free_memory 2,555,753,592
----------------
Query_cache ...
0
You can pivot on a rownumber. This can be tedious for large numbers of columns though.
SELECT ID, [Text], [1] AS Location1, [2] AS Location1, [3] AS Location3
FROM (
SELECT t.id as ID,
j.text as Text,
l.location as Location,
rn = ROW_NUMBER() OVER (PARTITION BY ID, [Text] ORDER BY (SELECT 1))
FROM [text] t WITH (nolock)
...
1
I'm wondering, what is the name of the thing that he used here where he like... split the employee table into 2 virtual tables for the sake of the query?
In this query, the author uses 2 copies of the source table. 2 absolutely independent copies. He gives unique aliases (temporary names) to each copy - this allows us to distinguish from which copy of the ...
1
I just read your examples closer and understand your question better now. Your second example that you were doing uses a subquery but the first example your professor was doing is called an implicit inner join. It's just a shorthand way to write FROM employee EMP INNER JOIN employee SUP ON SUP.employee_ID = EMP.supervisor_ID like @stickybit commented.
3
Hopefully this should provide an answser:
This implements "common" relational division:
SELECT DISTINCT grpid
FROM groups AS g
GROUP BY grpid, changeDate
HAVING NOT EXISTS
( SELECT 1
FROM groups AS gi
WHERE gi.grpid = g.grpid
AND gi.changeDate = g.changeDate
AND NOT EXISTS
( SELECT 1
FROM ...
2
You can use a inner join for that
Fyi mysql 8 duesn't like groups as table name, and must be put in backticks
And lines id = 2 and and id = 4, are also "identical, and fall under your rule
create table groups ( id int, grpid int , changeDate date, userid int, pct double , hobby int );
insert into groups(id , grpid , changeDate ,userid ,pct ,...
0
SELECT DISTINCT grpid
FROM TableWithGroups
GROUP BY grpid, userid, pct, hobby
HAVING COUNT(*) > 1
This uses the GROUP BY and HAVING clauses to respectively group up the values on the fields you specified to condense the duplicates into one row and filter where the count of rows condensed in each grouping was greater than 1 (in other words where there ...
Top 50 recent answers are included
