New answers tagged select
1
Looks like this can be done with a single SQL statement, actually, something like this:
select case when n2 = 0 then 0 else n1/n2 from (
select
h.hireId,
count(*) as n1,
sum(case when r.hireResponse in (0,1) then 1 else 0 end) as n2
from
NewHire h, Hire_Response r
where
h.hireId = r.hireId
group by
h.hireId
)
I don't have a SQL ...
2
You need an additional select just after your insert.
declare @t table (Percentage int)
DECLARE @acc INT
SET @acc = 1
DECLARE @max INT
select @max = max(HireID) from NewHire
WHILE (@acc <= @max)
BEGIN
IF (@acc in (select HireID from NewHire))
BEGIN try
insert into @t
select
...
1
See this answer. No, the order you put the conditions in the WHERE clause doesn't matter: In MySQL, does the order of the columns in a WHERE clause affect query performance?
If id is not nullable, you can replace the count(id) with count(*) and try adding an index on (_campaign, session, timeCreated).
ALTER TABLE mDelivered
ADD INDEX ...
2
After further tinkering, I worked it out. If I'm correct, then it's stupidly simple.
Additional tables can be added to my scenario if:
they are added as LEFT JOINS
they are added after the INNER JOINS
I'm still trying to wrap my head around exactly how JOIN functions, but it makes sense to me that the first three joins are narrowing down the selection. ...
1
Here is the query
SELECT C.* FROM
(SELECT nid,COUNT(1) fullcount
FROM node
INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
GROUP BY nid) A INNER JOIN
(SELECT nid,COUNT(1) goodcount
FROM node
INNER JOIN field_data_dir_phys_category AS category ON node.nid = category.entity_id
WHERE ...
2
Deadlocking by SELECTs can be done in a variety of ways. I have written posts about them
You can have SELECTs get deadlocked by UPDATEs and DELETEs
Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
You can have UPDATEs and DELETEs blocked by SELECTs
How are DB locks tied to connections and sessions?
Is Oracle DB immune to the InnoDB deadlocks ...
-1
Change you query to :
SELECT MAX(A.Row) FROM
(SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) as Row FROM Users) as A
Try this mate.
4
Another approach (SQLfiddle):
WITH ToUpdate AS
(
SELECT
m.Amt,
NewAmt =
CASE
WHEN m.UserID = 'Admin'
THEN SUM(m.Amt) OVER ()
ELSE 0
END
FROM dbo.Main AS m
WHERE m.UserID = 'Admin'
OR EXISTS (SELECT 1 FROM dbo.Filtered AS f WHERE m.UserID = f.UserID)
...
4
WITH Total AS
(
SELECT
SUM(m.AMT) AS AMT
FROM dbo.Main m
INNER JOIN dbo.Filtered f
ON m.UserID = f.UserID
)
UPDATE mn
SET AMT = CASE mn.UserID
WHEN 'Admin' THEN (mn.AMT + t.AMT)
ELSE 0
END
FROM dbo.Main mn
CROSS APPLY Total t
WHERE EXISTS (SELECT fl.UserID
FROM dbo.Filtered fl
...
3
Below is how you can achieve
Required results --
select *
from [Output]
order by UserID
Query to achieve Required results --
select UserID
,Amt = case
when UserID in (
select UserID
from Filtered
)
then '0.00'
when UserID = 'Admin'
then (
...
1
If you are only expecting one or zero rows back, then this would also work:
SELECT
max(col1) col1,
max(col2) col2,
1 AS query_id
FROM
players
WHERE
username='foobar';
This will return one row with all values having null except query_id if no row is found.
4
SELECT col1,
col2,
col3,
1 AS query_id
FROM players
WHERE username='foobar'
union all
select null,
null,
null,
1
where not exists (select 1 from players where username = 'foobar');
Or as an alternative (might be faster as no second subselect is required):
with qid (query_id) as (
values (1)
)
select p.*, ...
Top 50 recent answers are included
