How to select distinct values from a column with a newly created key as reference

Question

I am creating a distinct key by concatenating two different columns of a table. Now the key which I have created can be repeated, for example column A is roll number of student and column B is the optional subject in which he is enrolled. Now a student can be enrolled in multiple subjects as well as single optional subject, I want only those students roll number who are enrolled in only single optional subjects.

with the logic I am using I am getting the following error,

Message:ERROR: window functions not allowed in WHERE clause

Please help

SELECT
    CONCAT(roll_number, subject) as key,
    count (key) over(partition by key) as values
FROM
    subject_data
WHERE
    roll_number is not null and subject is not null
    and values = 1

when I am using HAVING clause it is still throwing same error

Which are you using, MySQL or Oracle? Please don't spam unrelated tags. — Barmar, Mar 21 '23 at 20:01
Use `HAVING values = 1`. You can't refer to column aliases in the `WHERE` clause. — Barmar, Mar 21 '23 at 20:01

score 0 · Answer 1 · answered Mar 21 '23 at 20:06

You can use multiple CTE, to get your result

WITH CTE as
(SELECT CONCAT(roll_number, subject) as key1 FROM subject_data 
WHERE roll_number is not null and subject is not NULL), CTE2 AS
(SELECT key1, count(key1) over(partition by key1) as val FROM CTE)
SELECT  key1, val FROM CTE2
WHERE val = 1

How to select distinct values from a column with a newly created key as reference

1 Answers1