Query not working as expected...
От | Titus J. Anderson |
---|---|
Тема | Query not working as expected... |
Дата | |
Msg-id | 20020517145623.A113708@athena.louisville.edu обсуждение исходный текст |
Ответы |
Re: Query not working as expected...
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-general |
I don't know if this is a bug or my lack of understanding of how the query is working. I have this table: test=# select * from dates2; did | date | type | timestamp | cid ------+------------+--------------+------------------------+------ 5647 | 2002-05-06 | Open | 2002-05-06 13:28:35-04 | 1039 5648 | 2002-07-27 | Schedule | 2002-05-06 13:28:35-04 | 1039 5708 | 2002-05-15 | Edit | 2002-05-15 11:12:09-04 | 1039 5709 | 2002-05-15 | Edit | 2002-05-15 11:39:59-04 | 1039 5710 | 2002-05-15 | Edit | 2002-05-15 11:41:01-04 | 1039 5711 | 2002-05-15 | Edit | 2002-05-15 11:41:37-04 | 1039 5712 | 2002-05-15 | Open | 2002-05-15 14:34:32-04 | 1053 5713 | 2002-05-26 | Schedule | 2002-05-15 14:34:32-04 | 1053 5714 | 2002-05-15 | Open | 2002-05-15 14:36:00-04 | 1054 5715 | 2002-05-15 | Schedule | 2002-05-15 14:36:00-04 | 1054 5716 | 2002-05-15 | Edit | 2002-05-15 14:36:25-04 | 1054 5717 | 2002-05-15 | Edit | 2002-05-15 14:37:06-04 | 1054 5718 | 2002-05-15 | Edit | 2002-05-15 14:37:13-04 | 1054 5728 | 2002-05-15 | Approve | 2002-05-15 14:45:49-04 | 1054 5729 | 2002-05-19 | Notify | 2002-05-15 14:45:59-04 | 1054 5730 | 2002-05-21 | Complete | 2002-05-15 14:46:28-04 | 1054 5731 | 2002-05-15 | Close | 2002-05-15 14:46:28-04 | 1054 5732 | 2002-05-27 | Schedule | 2002-05-15 15:24:30-04 | 1039 5733 | 2002-05-15 | Edit | 2002-05-15 11:41:37-04 | 1055 5734 | 2002-05-15 | Edit | 2002-05-15 11:41:01-04 | 1055 5735 | 2002-05-15 | Edit | 2002-05-15 11:39:59-04 | 1055 5736 | 2002-05-15 | Edit | 2002-05-15 11:12:09-04 | 1055 5737 | 2002-05-06 | Open | 2002-05-06 13:28:35-04 | 1055 5738 | 2002-05-27 | Schedule | 2002-05-15 15:24:30-04 | 1055 5739 | 2002-07-27 | Schedule | 2002-05-06 13:28:35-04 | 1055 5740 | 2002-07-23 | Schedule | 2002-05-15 15:33:00-04 | 1055 (26 rows) I want to extract only the most recently added rows that are uniquely defined by cid and type. test=# select distinct on (cid,type) * from dates2 order by cid,type,timestamp desc; did | date | type | timestamp | cid ------+------------+--------------+------------------------+------ 5711 | 2002-05-15 | Edit | 2002-05-15 11:41:37-04 | 1039 5647 | 2002-05-06 | Open | 2002-05-06 13:28:35-04 | 1039 5732 | 2002-05-27 | Schedule | 2002-05-15 15:24:30-04 | 1039 5712 | 2002-05-15 | Open | 2002-05-15 14:34:32-04 | 1053 5713 | 2002-05-26 | Schedule | 2002-05-15 14:34:32-04 | 1053 5728 | 2002-05-15 | Approve | 2002-05-15 14:45:49-04 | 1054 5731 | 2002-05-15 | Close | 2002-05-15 14:46:28-04 | 1054 5730 | 2002-05-21 | Complete | 2002-05-15 14:46:28-04 | 1054 5718 | 2002-05-15 | Edit | 2002-05-15 14:37:13-04 | 1054 5729 | 2002-05-19 | Notify | 2002-05-15 14:45:59-04 | 1054 5714 | 2002-05-15 | Open | 2002-05-15 14:36:00-04 | 1054 5715 | 2002-05-15 | Schedule | 2002-05-15 14:36:00-04 | 1054 5733 | 2002-05-15 | Edit | 2002-05-15 11:41:37-04 | 1055 5737 | 2002-05-06 | Open | 2002-05-06 13:28:35-04 | 1055 5740 | 2002-07-23 | Schedule | 2002-05-15 15:33:00-04 | 1055 (15 rows) Now, I want only those rows from the subquery that have a date between 2002-05-17 and 2002-05-31 and a type of "Schedule". test=# select * from (select distinct on (cid,type) * from dates2 order by cid,type,timestamp desc) as foo where type='Schedule' and foo.date between '2002-05-17' and '2002-05-31'; did | date | type | timestamp | cid ------+------------+--------------+------------------------+------ 5732 | 2002-05-27 | Schedule | 2002-05-15 15:24:30-04 | 1039 5713 | 2002-05-26 | Schedule | 2002-05-15 14:34:32-04 | 1053 5738 | 2002-05-27 | Schedule | 2002-05-15 15:24:30-04 | 1055 (3 rows) Notice the last row. The row with did 5738 is NOT in the result set from the second query, which is the subquery of the above command. So why is this showing up in the results? Anyone have an idea? -- Titus Anderson
В списке pgsql-general по дате отправления: