I migrated my database from SQLite to Postgres. Before with SQlite I was able to eleminiate duplicates in my query output based on an ORDER BY followed by GROUP BY. The order by is required because I do some calculation on the data.
Lets assume I have the following to tables T1 Option
| Symbol | date | strike | lastprice |
|---|---|---|---|
| A | 2022-08-05 | 20 | 1 |
| A | 2022-08-04 | 20 | 1.1 |
| B | 2022-08-05 | 18 | 2 |
| B | 2022-08-04 | 18 | 2.1 |
| A | 2022-08-05 | 21 | 1.1 |
| A | 2022-08-05 | 22 | 1.2 |
| B | 2022-08-05 | 18.5 | 1 |
| B | 2022-08-05 | 19 | 1.2 |
T2 Stock
| Symbol | date | price |
|---|---|---|
| A | 2022-08-05 | 19 |
| A | 2022-08-04 | 19.5 |
| B | 2022-08-05 | 18.5 |
| B | 2022-08-04 | 18.4 |
I run the following query
select s.price/o.strike as CalculatedValue, *
from "Stock" s
inner join "Option" o
on (o.symbol = s.symbol) and s."date" = o."date"
WHERE
o."date" = (select x."date" FROM "Option" x where x."date" notnull ORDER BY x."date" DESC LIMIT 1)
order by CalculatedValue desc
The output is
| calculatedValue | symbol | date | price | symbol | date | strike | lastprice |
|---|---|---|---|---|---|---|---|
| 1.0277777777777777 | B | 2022-08-05 | 18.5 | B | 2022-08-05 | 18.0 | 2.0 |
| 1.0 | B | 2022-08-05 | 18.5 | B | 2022-08-05 | 18.5 | 1.0 |
| 0.9736842105263158 | B | 2022-08-05 | 18.5 | B | 2022-08-05 | 19.0 | 1.2 |
| 0.95 | A | 2022-08-05 | 19.0 | A | 2022-08-05 | 20.0 | 1.0 |
| 0.9047619047619048 | A | 2022-08-05 | 19.0 | A | 2022-08-05 | 21.0 | 1.1 |
| 0.8636363636363636 | A | 2022-08-05 | 19.0 | A | 2022-08-05 | 22.0 | 1.2 |
what I need is the following
| calculatedValue | symbol | date | price | symbol | date | strike | lastprice |
|---|---|---|---|---|---|---|---|
| 1.0277777777777777 | B | 2022-08-05 | 18.5 | B | 2022-08-05 | 18.0 | 2.0 |
| 0.95 | A | 2022-08-05 | 19 | A | 2022-08-05 | 20.0 | 1.0 |
With SQLite I just uses group by s.symbol, in postgres this gives me an error. Also I tried with a distinct on subquery which completely ignores my ORDER BY...
Any idea how this can be done )?
CodePudding user response：
You need use the partition by (s.symbol) in the query with ORDER BY ( calulated price) desc and with the filter with the value 1 so the you can get what you need
select * from (
select row_number() OVER( PARTITION BY s.symbol ORDER BY (s.price/o.strike) desc) as ord, s.price/o.strike as CalculatedValue,s.symbol,s.date,price,strike,lastprice
from "Stock" s
inner join "Option" o
on (o.symbol = s.symbol) and s."date" = o."date"
WHERE
o."date" = (select x."date" FROM "Option" x where x."date" notnull ORDER BY x."date" DESC LIMIT 1)
) as drt where ord=1
order by CalculatedValue desc