Question 259212 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Query Error: error: syntax error at or near "SELECT"

I am trying 8weeksqlchallenge, and I usually work with MySQL and the error codes are quite cleaner there, but not so much on dbfiddle.com

This is my schema

CREATE SCHEMA dannys_diner;
SET search_path = dannys_diner;

CREATE TABLE sales 
(
    "customer_id" VARCHAR(1),
    "order_date" DATE,
    "product_id" INTEGER
);

INSERT INTO sales ("customer_id", "order_date", "product_id")
VALUES
  ('A', '2021-01-01', '1'),
  ('A', '2021-01-01', '2'),
  ('A', '2021-01-07', '2'),
  ('A', '2021-01-10', '3'),
  ('A', '2021-01-11', '3'),
  ('A', '2021-01-11', '3'),
  ('B', '2021-01-01', '2'),
  ('B', '2021-01-02', '2'),
  ('B', '2021-01-04', '1'),
  ('B', '2021-01-11', '1'),
  ('B', '2021-01-16', '3'),
  ('B', '2021-02-01', '3'),
  ('C', '2021-01-01', '3'),
  ('C', '2021-01-01', '3'),
  ('C', '2021-01-07', '3');
 
CREATE TABLE menu 
(
    "product_id" INTEGER,
    "product_name" VARCHAR(5),
    "price" INTEGER
);

INSERT INTO menu ("product_id", "product_name", "price")
VALUES
  ('1', 'sushi', '10'),
  ('2', 'curry', '15'),
  ('3', 'ramen', '12');
  

CREATE TABLE members 
(
    "customer_id" VARCHAR(1),
    "join_date" DATE
);

INSERT INTO members ("customer_id", "join_date")
VALUES
  ('A', '2021-01-07'),
  ('B', '2021-01-09');

And ask was to find the first product purchased by each customer, so I thought of going with subquery with following code, but it returned

Query Error: error: syntax error at or near "SELECT"

SELECT sales.customer_id, sales.product_id
FROM dannys_diner.sales 
WHERE sales.order_date = SELECT MIN(sales.order_date)
                         FROM dannys_diner.sales 
                         GROUP BY sales.customer_id;
Tags:

3

Answers


  1. 0

    You need ( and ) around the sub-query — like this

    SELECT sales.customer_id, sales.product_id
FROM dannys_diner.sales
WHERE sales.order_date = ( 
  SELECT MIN(sales.order_date)
  FROM dannys_diner.sales 
  GROUP BY sales.customer_id
);

    As some have noted there might be data that would cause a problem a join solves this because you can make sure the min number is by customer_id — like this

    SELECT sales.customer_id, sales.product_id
FROM dannys_diner.sales
JOIN ( 
  SELECT MIN(sales.order_date) as order_date, sales.customer_id
  FROM dannys_diner.sales 
  GROUP BY sales.customer_id
) as x on x.order_date = sales.order_date and x.customer_id = sales.customer_id ;

    Note if you compare a fiddle using my code

    https://dbfiddle.uk/CSKDTxd6

    To Joel’s answer (using a windowing function) you can see we get different results — I will return more than one row if they have the same date — his will only ever return one row. Pick which one meets your requirements:

    Login or Signup to reply.
  2. 0

    There are two syntax errors here.

    First, the subquery needs to be enclosed in parentheses. This is easily remedied.

    Second, in order to be valid on the right-hand side of the equality comparison with sales.order_date, the subquery must be guaranteed to always return a single value. The GROUP BY clause in the subquery breaks this, because it allows for separate minimum order dates per customer id. This is trickier to resolve, because it means re-thinking the structure of the query.

    Instead, I suggest using a row_number() window function you to identify rows by date, and then show the product from the rows without needing a correlated per-row subquery or JOIN (in other words, it should be much faster):

    SELECT customer_id, product_id
FROM (
    select sales.customer_id, sales.product_id
        , row_number() over (partition by customer_id order by sales.order_date) rn
    from dannys_diner.sales
) t
WHERE rn = 1

    https://dbfiddle.uk/YGdol5Rd

    Login or Signup to reply.
  3. 0

    you can join the subqzery to get your wanted result

    SELECT s1.customer_id, s1.product_id
FROM dannys_diner.sales s1 JOIN
  (SELECT customer_id,MIN(order_date) min_date
FROM dannys_diner.sales 
GROUP BY sales.customer_id) s2  ON s1.customer_id = s2.customer_id
  ANd s1.order_date = s2.min_date
  
;
    customer_id product_id
    A 1
    A 2
    B 2
    C 3
    C 3 
    SELECT 5

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search