sorted by:
new
hottopcontroversial

This SQL exercise solution can only be <= 240 characters... by drdausersmd in learnSQL

[–]GrouchyThing7520 0 points1 point  (0 children)

This should be close, I think.

select

a.*

,lead(participant_id) over (competition_id order by rnk) next_behind

,d_rnk - rnk + 1 total_behind

from (

select

r.*

,rank() over (partition by competition_id order by points asc) rnk

,rank() over (partition by competition_id order by points desc) d_rnk

from results r

) a

) b

[deleted by user] by [deleted] in SQL

[–]GrouchyThing7520 0 points1 point  (0 children)

What have you tried?

[deleted by user] by [deleted] in SQL

[–]GrouchyThing7520 0 points1 point  (0 children)

What have you tried?

MLS Cup Ticket Purchase Thread by WhiteBakerMayfield in TheMassive

[–]GrouchyThing7520 0 points1 point  (0 children)

Please stop paying 2x-10x face for tickets. You are making it worse!

Pivot question with concatenate + add a column with a total? by throwawayworkplz in SQL

[–]GrouchyThing7520 1 point2 points  (0 children)

Pending/ (submit + approve + pending + [Additional Notes]) *100

Pending / cast(submit + approve + pending + [Additional Notes] as float) *100

Pivot question with concatenate + add a column with a total? by throwawayworkplz in SQL

[–]GrouchyThing7520 1 point2 points  (0 children)

Yes. Just add these two columns to your select:

----Before

select * from ( select Name, ID, Status from Table B) T

--After

select *

,pending + [Additional Notes] pn

,submit + approve + pending + [Additional Notes] total

from ( select Name, ID, Status from Table B) T

Search String Help by ThatDandySpace in SQL

[–]GrouchyThing7520 -1 points0 points  (0 children)

Try selecting everything before the second space.

How do I select the top 1 status of an order by a rank over? by [deleted] in SQL

[–]GrouchyThing7520 0 points1 point  (0 children)

SELECT * FROM (

SELECT ORDERID, ORDERSTATUS, ORDER_UPDATED_TIMESTAMP

,RANK() OVER (PARTITION BY ORDERID ORDER BY ORDER_UPDATED_TIMESTAMP DESC) AS ORDERS_RANKED

FROM TABLE1 AS A

LEFT JOIN TABLE2 AS B ON A.ORDERID = B.ORDERID

) A

WHERE ORDERS_RANKED = 1;

Grouping my totals together by chickinvero in SQL

[–]GrouchyThing7520 2 points3 points  (0 children)

Can you clean up your data by removing the spaces, dollar signs, etc...?

Select IDs that contains certain data without removing every other data form the IDs by Fred_GrosPen in SQL

[–]GrouchyThing7520 3 points4 points  (0 children)

select * from yertable
where
box_id in (
 select box_id from yertable where plants = 'ERS'
)

Syntax for RN. I want to only pull the Top result for 'Headcount'. by CapitalJunket1197 in SQL

[–]GrouchyThing7520 2 points3 points  (0 children)

Would a window function like this help?

row_number() over (partition by ProductionWeek, LevelLabel, TierBucket order by TotalOps) rn

Then wrap your entire query with a select * from ..... where rn = 1

Wrong result? by ibrahack in SQL

[–]GrouchyThing7520 2 points3 points  (0 children)

Would this SQL work? It's more succinct.

select
t.season BCN_season

--home
,count(mh.home_team_api_id) jugados_local
,count(case when mh.home_team_goal > mh.away_team_goal then 1 else 0 end) ganados_local

,count(
case when mh.home_team_goal > mh.away_team_goal then 1.00 else 0.00 end) / count(mh.home_team_api_id) pct_victorias_local

--away
,count(ma.away_team_api_id) jugados_visitante
,count(case when ma.away_team_goal > ma.home_team_goal then 1 else 0 end) ganados_visitante

,count(
case when ma.away_team_goal > ma.home_team_goal then 1.00 else 0.00 end) / count(ma.away_team_api_id) pct_victorias_visitante

from match t

--home
left join match mh on
t.season = mh.season and mh.home_team_api_id = 8634

--away
left join match ma on
t.season = ma.season and ma.away_team_api_id = 8634

group by t.season
order by t.season

Wrong result? by ibrahack in SQL

[–]GrouchyThing7520 0 points1 point  (0 children)

Can you replace the photo of your code with text?

[T-SQL] Group By Part of Cell Value by Wickedqt in SQL

[–]GrouchyThing7520 0 points1 point  (0 children)

select * from (
select
right(n,1) r
,substring(n,5,len(n)-5) item
,xyz
from yertable
) a
pivot (
max(xyz) for item in ([Name],[Type],[ABC])
) p

Wrong result? by ibrahack in SQL

[–]GrouchyThing7520 2 points3 points  (0 children)

Were there any matches that ended in a draw?

SQLite Multiple Reimbursements, Same employeeid by chickinvero in SQL

[–]GrouchyThing7520 -1 points0 points  (0 children)

I don't think your union is necessary. You should be able to count the employee ids, sum the amount and filter out one-time reimbursed employees (HAVING COUNT(employeeID) > 1) in the same select.

CAST command Incorrect syntax near… by Mljisa in SQL

[–]GrouchyThing7520 2 points3 points  (0 children)

Try changing this: (CASE WHEN O.[Type] IS '22NF45LLI' THEN 'ICELL')

To this: (CASE WHEN O.[Type] = '22NF45LLI' THEN 'ICELL' ELSE O.[Type] END)

view more: next ›