python (65.1k questions)

javascript (44.2k questions)

reactjs (22.7k questions)

java (20.8k questions)

c# (17.4k questions)

html (16.3k questions)

r (13.7k questions)

android (12.9k questions)

Questions - common-table-expression

Max and Group by functions in SQL Server

my sql got a bit rusty and I was wondering if there is a simple solution for my ask. I'm trying to get two unique UUIDs with with MAX ModifiedTime. Below is my code that I tried to simplify as much as...
test-img

user7948585

sql

group-by

max

common-table-expression

Votes: 0

Answers: 3

Latest Answer

Sub-query and inner join takes care of this. SELECT t1.uuid ,t1.Description ,t1.IsHLVE ,t1.modifiedtime FROM tblConstructSecureIntelProjectIncidents as t1 INNER JOIN ( SELECT ...
test-img

Edward Radcliffe

How to get rows having maximum occurrence using SQL

I have a participant table: I have written a CTE which gives the following table: CTE code: with cte as ( select t.per1,t.per2,t.met1,concat(t.per1,t.per2) concated from ( select p1.person_id per1, ...
test-img

Ashish

sql

sql-server

common-table-expression

Votes: 0

Answers: 1

Latest Answer

We can use the COUNT() analytic in your innermost query to find the counts of pairs of meeting attendees: WITH cte AS ( SELECT p1.person_id per1, p1.meeting_id met, p2.person_id per2, C...
test-img

Tim Biegeleisen

ParseException: SQL CTE

result = aml_identity_g.connectedComponents() conn_comps = result.select("id", "component",'type') \ .createOrReplaceTempView("components") display(result) Which crea...
test-img

Betafish

apache-spark-sql

databricks

common-table-expression

databricks-sql

Votes: 0

Answers: 1

Latest Answer

You really don't need CTE in this query - it should be enough to have normal SELECT. So it should be something like this: create table temptable as ( select component, count(case when type = 'Perso...
test-img

Alex Ott

Error in SQL statement: ParseException: SQL CTE

%sql create table temptable as with dupes as (select component, count(case when type = 'Person' then 1 end) person_ct from components group by component having person_ct...
test-img

Betafish

sql

apache-spark-sql

databricks

common-table-expression

Votes: 0

Answers: 1

Latest Answer

The text, image, and ntext data types cannot be used in a HAVING clause. you cannot use an alias name in having a clause. you have to give same count expression instead of person_ct.. Code should look...
test-img

Pratik Lad

Posts

Questions

Blogs

Jobs