# Top-K Projection Examples

The following examples show how to query a table with two Top-K projections for the most-recent trade and last trade of the day for each stock symbol.

1. Create a table that contains information about individual stock trades:
• Stock symbol
• Timestamp
• Price per share
• Number of shares
```=> CREATE TABLE trades(
symbol CHAR(16) NOT NULL,
trade_time TIMESTAMP NOT NULL,
price NUMERIC(12,4),
volume INT )
PARTITION BY (EXTRACT(year from trade_time) * 100 +
EXTRACT(month from trade_time));```
2. Load data into the table:
```INSERT INTO trades VALUES('AAPL','2010-10-10 10:10:10'::TIMESTAMP,100.00,100);
INSERT INTO trades VALUES('AAPL','2010-10-10 10:10:10.3'::TIMESTAMP,101.00,100);
INSERT INTO trades VALUES ('AAPL','2011-10-10 10:10:10.5'::TIMESTAMP,106.1,1000);
INSERT INTO trades VALUES ('AAPL','2011-10-10 10:10:10.2'::TIMESTAMP,105.2,500);
INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.2'::TIMESTAMP,42.01,400);
INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.3'::TIMESTAMP,42.02,1000);
INSERT INTO trades VALUES ('HPQ','2012-10-10 10:10:10.4'::TIMESTAMP,42.05,100);
COMMIT;```
3. Create two Top-K projections that obtain the following information from the `trades` table:
4. For each stock symbol, return the most recent trade.

```=> CREATE PROJECTION trades_topk_a AS SELECT symbol, trade_time last_trade, price last_price
FROM trades LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);

=> SELECT symbol, trade_time last_trade, price last_price FROM trades
LIMIT 1 OVER(PARTITION BY symbol ORDER BY trade_time DESC);

symbol      |      last_trade       | last_price
------------------+-----------------------+------------
HPQ              | 2012-10-10 10:10:10.4 |    42.0500
AAPL             | 2011-10-10 10:10:10.5 |   106.1000
(2 rows)
```

For each stock symbol, return the last trade on each trading day.

```=> CREATE PROJECTION trades_topk_b
AS SELECT symbol, trade_time::DATE trade_date, trade_time, price close_price, volume
FROM trades LIMIT 1 OVER(PARTITION BY symbol, trade_time::DATE ORDER BY trade_time DESC);```
```=> SELECT symbol, trade_time::DATE trade_date, trade_time, price close_price, volume
FROM trades LIMIT 1 OVER(PARTITION BY symbol, trade_time::DATE ORDER BY trade_time DESC);

symbol      | trade_date |      trade_time       | close_price | volume
------------------+------------+-----------------------+-------------+--------
HPQ              | 2012-10-10 | 2012-10-10 10:10:10.4 |     42.0500 |    100
AAPL             | 2011-10-10 | 2011-10-10 10:10:10.5 |    106.1000 |   1000
AAPL             | 2010-10-10 | 2010-10-10 10:10:10.3 |    101.0000 |    100
(3 rows)
```

In each scenario, Vertica redirects queries on the `trades` table to the appropriate Top-K projection and returns the aggregated data from them. As additional data is loaded into this table, Vertica pre-aggregates the new data and updates the Top-K projections, so queries always return with the latest data.