Help Center/ GaussDB(DWS)/ Developer Guide/ Standard data warehouse (9.1.0.x)/ GaussDB(DWS) Data Query/ GaussDB(DWS) Single-Table Query
Updated on 2024-12-18 GMT+08:00
View PDF
Share

GaussDB(DWS) Single-Table Query

Example table:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
CREATE TABLE newproducts
(
product_id INTEGER NOT NULL,
product_name VARCHAR2(60),
category VARCHAR2(60),
quantity  INTEGER
)
WITH (ORIENTATION = COLUMN) DISTRIBUTE BY HASH(product_id);


INSERT INTO newproducts VALUES (1502, 'earphones', 'electronics',150);
INSERT INTO newproducts VALUES (1601, 'telescope', 'toys',80);
INSERT INTO newproducts VALUES (1666, 'Frisbee', 'toys',244);
INSERT INTO newproducts VALUES (1700, 'interface', 'books',100);
INSERT INTO newproducts VALUES (2344, 'milklotion', 'skin care',320);
INSERT INTO newproducts VALUES (3577, 'dumbbell', 'sports',550);
INSERT INTO newproducts VALUES (1210, 'necklace', 'jewels', 200);

Simple Queries

Run the SELECT... FROM... statement to obtain the result from the database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
SELECT category FROM newproducts;
  category
------------
 electr
 sports
 jewels
 toys
 books
 skin care
 toys
(7 rows)

Filtering Test Results

Run the WHERE statement to filter the query result and find the queried part.

1
2
3
4
5
6
 
SELECT * FROM newproducts WHERE category='toys';
 product_id | product_name | category | quantity
------------+--------------+----------+----------
       1601 | telescope    | toys     |       80
       1666 | Frisbee      | toys     |      244
(2 rows)

Sorting Results

Use the ORDER BY statement to sort query results.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
SELECT product_id,product_name,category,quantity FROM newproducts ORDER BY quantity DESC;
 product_id | product_name |  category   | quantity
------------+--------------+-------------+----------
       3577 | dumbbell     | sports      |      550
       2344 | milklotion   | skin care   |      320
       1666 | Frisbee      | toys        |      244
       1210 | necklace     | jewels      |      200
       1502 | earphones    | electronics |      150
       1700 | interface    | books       |      100
       1601 | telescope    | toys        |       80
(7 rows)

Limiting the Number of Query Results

If you want the query to return only part of the result, you can use the LIMIT statement to limit the number of records returned in the query result.

1
2
3
4
5
6
7
8
9
 
SELECT product_id,product_name,category,quantity FROM newproducts ORDER BY quantity DESC limit 5;
 product_id | product_name |  category   | quantity
------------+--------------+-------------+----------
       3577 | dumbbell     | sports      |      550
       2344 | milklotion   | skin care   |      320
       1666 | Frisbee      | toys        |      244
       1210 | necklace     | jewels      |      200
       1502 | earphones    | electronics |      150
(5 rows)

Aggregated Query

If you want query data comprehensively, you can use the GROUP BY statement and aggregate functions to construct an aggregated query.

1
2
3
4
5
6
7
8
9
 
SELECT category, string_agg(quantity,',') FROM newproducts group by category;
  category   | string_agg
-------------+------------
 toys        | 80,244
 books       | 100
 sports      | 550
 jewels      | 200
 skin care   | 320
 electronics | 150
Parent topic: GaussDB(DWS) Data Query