CODEWITHSUNDEEP
sqlcommon-table-expression
Nik
Nik

Reputation: 381

Issue with CTE (with..as construction)

I am trying to learn CTE constructions with online excercises.

Here is a simplified version of the query:

with mr as 
(
     select min(ram) 
     from pc
)
select model 
from pc 
where ram = mr 
  and speed = (select max(speed) from pc where ram = mr)

It fails with error:

No column name was specified for column 1 of 'mr'

I tried to solve the problem by adding an alias to aggregate column

with mr as 
(
    select min(ram) as v 
    from pc
) 
select model 
from pc 
where ram = mr.v 
  and speed = (select max(speed) from pc where ram = mr.v)

Now it fails with

The multi-part identifier "mr.v" could not be bound

So why the queries above produce errors and- what is a right way to write such queries?

Upvotes: 0

Views: 114

Answers (3)

tripcode
tripcode

Reputation: 74

An alternate way to tackle this would be to define both of your subqueries as CTEs.

with mr as (select min(ram) min_ram from pc),
     ms as (select max(speed) as max_speed from pc where ram = (select minram from mr))

select pc.model 
from pc
    left join mr on pc.ram = mr.min_ram
    left join ms on pc.speed = ms.max_speed

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271181

If you want a shorter query, then assuming that you want one row, then use order by and some form of limit:

select pc.*
from pc
order by ram desc, speed asc
fetch first 1 row only;

Upvotes: 1

forpas
forpas

Reputation: 164214

mr is not a column, but a table-like query and you must refer to it as such:

with mr as (select min(ram) minram from pc) 
select model from pc 
where ram=(select minram from mr) 
and speed=(select max(speed) from pc where ram = (select minram from mr))

Upvotes: 1

Related Questions