[SQL] sub query and AS
Hi,
I like PostgreSQL for many reasons, one of them is the possibility to
use sub query everywhere. Now I've found where it doesn't support them.
I would like to use a AS (sub query) form.
This is an example:
First the subquery:
select substr(descr, 7, length(descr)-8)
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1
) as a
) as b;
substr
-
"800900420" int,"800900450" int,"800900480" int,"800900570"
int,"800900590" int,"800900622" int,"800900630" int,"800900644"
int,"800900688" int,"800900950" int
(1 row)
Then the wishing one:
itv2=#
select *
FROM crosstab('select uscita,freephone,id from calendario order by
1','select distinct freephone from calendario order by 1')
-- following AS fails
AS (select 'uscita int, ' || substr(descr, 7, length(descr)-8)
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1) as a
) as b;
);
ERROR: syntax error at or near "select"
LINE 4: ...stinct freephone from calendario order by 1') as (select 'us...
More is on http://paste.scsys.co.uk/198877
I think that AS must evaluate the sub query in advance.
It could be possible to have such behavior?
Best regards, \ferz
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] left outer join only select newest record
Hi folks, I know I've seen posts like this before but Google isn't helping today. I have two tables, vehicle stock and tax requests. Each vehicle can be taxed more than once, but I only want to pull in the most recent tax request - the one with the highest ud_id. I have the following, which obviously returning multiple records which then appears that the same vehicle is in stock multiple times. How can I make it so we only show each vehicle once, showing the most recent tax request details. select * from stock_details s left outer join used_diary u on s.s_registration = u.ud_registration; -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
Gary, You describe two tables vehicle stock and tax requests. The former has a one-to-many relationship wit the second one, right? But your query involves stock details and used_diary. What is the relationship of these two new tables to the previous ones? Could you please kindly supply an example of what you have and of the desired output? For me it would be easier... Best, Oliver - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:27 AM Subject: [SQL] left outer join only select newest record Hi folks, I know I've seen posts like this before but Google isn't helping today. I have two tables, vehicle stock and tax requests. Each vehicle can be taxed more than once, but I only want to pull in the most recent tax request - the one with the highest ud_id. I have the following, which obviously returning multiple records which then appears that the same vehicle is in stock multiple times. How can I make it so we only show each vehicle once, showing the most recent tax request details. select * from stock_details s left outer join used_diary u on s.s_registration = u.ud_registration; -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
2012/5/23 Gary Stainburn : > Hi folks, > > I know I've seen posts like this before but Google isn't helping today. > > I have two tables, vehicle stock and tax requests. Each vehicle can be taxed > more than once, but I only want to pull in the most recent tax request - the > one with the highest ud_id. > > I have the following, which obviously returning multiple records which then > appears that the same vehicle is in stock multiple times. How can I make it > so we only show each vehicle once, showing the most recent tax request > details. > > > select * from stock_details s > left outer join used_diary u on s.s_registration = u.ud_registration; select distinct on (s.s_registration) * ... order by u.ud_id desc or select * from stock_details s left join (select * from used_diary where (ud_id, ud_registration) = (select max(ud_id), ud_registration from used_diary group by ud_registration)) x on s.s_registration = x.ud_registration; Regards Pavel Stehule > > > -- > Gary Stainburn > Group I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
Appologies for not making it clearer. stock_details is simply a view of table stock, pulling in some lookup values. used_diary is the name of the table containing the tax requests. It's called the used_diary because it was the diary for taxing used vehicles. Here is a select to show the problem. There is one stock record and two tax records. What I'm looking for is how I can return only the second tax record, the one with the highest ud_id select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; s_stock_no | s_regno | s_vin | s_created | ud_id | ud_handover_date +-+---++---+-- UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 41892 | 2012-04-06 UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 42363 | 2012-05-16 (2 rows) On Wednesday 23 May 2012 10:37:31 Oliveiros d'Azevedo Cristina wrote: > Gary, > > You describe two tables vehicle stock and tax requests. The former has a > one-to-many relationship wit the second one, right? > > But your query involves stock details and used_diary. > > What is the relationship of these two new tables to the previous ones? > > Could you please kindly supply an example of what you have and of the > desired output? For me it would be easier... > > Best, > Oliver > > - Original Message - > From: "Gary Stainburn" > To: > Sent: Wednesday, May 23, 2012 10:27 AM > Subject: [SQL] left outer join only select newest record > > > Hi folks, > > > > I know I've seen posts like this before but Google isn't helping today. > > > > I have two tables, vehicle stock and tax requests. Each vehicle can be > > taxed > > more than once, but I only want to pull in the most recent tax request - > > the > > one with the highest ud_id. > > > > I have the following, which obviously returning multiple records which > > then > > appears that the same vehicle is in stock multiple times. How can I make > > it > > so we only show each vehicle once, showing the most recent tax request > > details. > > > > > > select * from stock_details s > > left outer join used_diary u on s.s_registration = u.ud_registration; > > > > > > -- > > Gary Stainburn > > Group I.T. Manager > > Ringways Garages > > http://www.ringways.co.uk > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
Gary Stainburn, 23.05.2012 11:47: Here is a select to show the problem. There is one stock record and two tax records. What I'm looking for is how I can return only the second tax record, the one with the highest ud_id select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; s_stock_no | s_regno | s_vin | s_created | ud_id | ud_handover_date +-+---++---+-- UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 41892 | 2012-04-06 UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 42363 | 2012-05-16 (2 rows) Something like: select * from ( select s_stock_no, s_regno s_vin, s_created, ud_id, ud_handover_date, row_number() over (partition by s_stock_no order by ud_id desc) as rn from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' ) t where rn = 1 The "partition by s_stock_no order" isn't really necessary as your where clause already limits that to a single stock_no. But in case you change that statement to return more than one stock_no in the future it will be necessary. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
Hello again, Gary, I don't know if this query works OK, i havent tried it. But, If I understood correctly this can be one way to do what you want. Could you please tell me if it worked and if it didn't why, so we can tweak it. Best, Oliver SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m, sec.s_creacted FROM (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; GROUP s_stock_no,s_regno,s_vin,s_created ) subq JOIN used_diary sec ON subq.m = sec.ud_id - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:47 AM Subject: Re: [SQL] left outer join only select newest record Appologies for not making it clearer. stock_details is simply a view of table stock, pulling in some lookup values. used_diary is the name of the table containing the tax requests. It's called the used_diary because it was the diary for taxing used vehicles. Here is a select to show the problem. There is one stock record and two tax records. What I'm looking for is how I can return only the second tax record, the one with the highest ud_id select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; s_stock_no | s_regno | s_vin | s_created | ud_id | ud_handover_date +-+---++---+-- UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 41892 | 2012-04-06 UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 42363 | 2012-05-16 (2 rows) On Wednesday 23 May 2012 10:37:31 Oliveiros d'Azevedo Cristina wrote: Gary, You describe two tables vehicle stock and tax requests. The former has a one-to-many relationship wit the second one, right? But your query involves stock details and used_diary. What is the relationship of these two new tables to the previous ones? Could you please kindly supply an example of what you have and of the desired output? For me it would be easier... Best, Oliver - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:27 AM Subject: [SQL] left outer join only select newest record > Hi folks, > > I know I've seen posts like this before but Google isn't helping today. > > I have two tables, vehicle stock and tax requests. Each vehicle can be > taxed > more than once, but I only want to pull in the most recent tax > request - > the > one with the highest ud_id. > > I have the following, which obviously returning multiple records which > then > appears that the same vehicle is in stock multiple times. How can I > make > it > so we only show each vehicle once, showing the most recent tax request > details. > > > select * from stock_details s > left outer join used_diary u on s.s_registration = u.ud_registration; > > > -- > Gary Stainburn > Group I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
Sorry, Gary, I made a mistake on the last column. It should be SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m, sec.ud_handover_date FROM (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; GROUP s_stock_no,s_regno,s_vin,s_created ) subq JOIN used_diary sec ON subq.m = sec.ud_id Best, Oliver - Original Message - From: "Oliveiros d'Azevedo Cristina" To: "Gary Stainburn" ; Sent: Wednesday, May 23, 2012 11:29 AM Subject: Re: [SQL] left outer join only select newest record Hello again, Gary, I don't know if this query works OK, i havent tried it. But, If I understood correctly this can be one way to do what you want. Could you please tell me if it worked and if it didn't why, so we can tweak it. Best, Oliver SELECT subq.s_stock_no,subq.s_regno,subq.s_vin,subq.s_created,subq.m, sec.s_creacted FROM (select s_stock_no, s_regno, s_vin, s_created, MAX(ud_id) as m from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; GROUP s_stock_no,s_regno,s_vin,s_created ) subq JOIN used_diary sec ON subq.m = sec.ud_id - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:47 AM Subject: Re: [SQL] left outer join only select newest record Appologies for not making it clearer. stock_details is simply a view of table stock, pulling in some lookup values. used_diary is the name of the table containing the tax requests. It's called the used_diary because it was the diary for taxing used vehicles. Here is a select to show the problem. There is one stock record and two tax records. What I'm looking for is how I can return only the second tax record, the one with the highest ud_id select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470'; s_stock_no | s_regno | s_vin | s_created | ud_id | ud_handover_date +-+---++---+-- UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 41892 | 2012-04-06 UL15470| YG12*** | KNADN312LC6** | 2012-05-21 09:15:31.569471 | 42363 | 2012-05-16 (2 rows) On Wednesday 23 May 2012 10:37:31 Oliveiros d'Azevedo Cristina wrote: Gary, You describe two tables vehicle stock and tax requests. The former has a one-to-many relationship wit the second one, right? But your query involves stock details and used_diary. What is the relationship of these two new tables to the previous ones? Could you please kindly supply an example of what you have and of the desired output? For me it would be easier... Best, Oliver - Original Message - From: "Gary Stainburn" To: Sent: Wednesday, May 23, 2012 10:27 AM Subject: [SQL] left outer join only select newest record > Hi folks, > > I know I've seen posts like this before but Google isn't helping > today. > > I have two tables, vehicle stock and tax requests. Each vehicle can be > taxed > more than once, but I only want to pull in the most recent tax > request - > the > one with the highest ud_id. > > I have the following, which obviously returning multiple records which > then > appears that the same vehicle is in stock multiple times. How can I > make > it > so we only show each vehicle once, showing the most recent tax request > details. > > > select * from stock_details s > left outer join used_diary u on s.s_registration = u.ud_registration; > > > -- > Gary Stainburn > Group I.T. Manager > Ringways Garages > http://www.ringways.co.uk > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sub query and AS
On Wed, May 23, 2012 at 5:24 AM, Ferruccio Zamuner wrote:
> Hi,
>
> I like PostgreSQL for many reasons, one of them is the possibility to use
> sub query everywhere. Now I've found where it doesn't support them.
>
> I would like to use a AS (sub query) form.
>
> This is an example:
>
> First the subquery:
>
> select substr(descr, 7, length(descr)-8)
> from (select string_agg('" int,"',freephone) as descr
> from (select distinct freephone
> from calendario order by 1
> ) as a
> ) as b;
>
> substr
> -
> "800900420" int,"800900450" int,"800900480" int,"800900570" int,"800900590"
> int,"800900622" int,"800900630" int,"800900644" int,"800900688"
> int,"800900950" int
> (1 row)
>
> Then the wishing one:
>
> itv2=#
> select *
> FROM crosstab('select uscita,freephone,id from calendario order by
> 1','select distinct freephone from calendario order by 1')
> -- following AS fails
> AS (select 'uscita int, ' || substr(descr, 7, length(descr)-8)
> from (select string_agg('" int,"',freephone) as descr
> from (select distinct freephone
> from calendario order by 1) as a
> ) as b;
> );
> ERROR: syntax error at or near "select"
> LINE 4: ...stinct freephone from calendario order by 1') as (select 'us...
>
> More is on http://paste.scsys.co.uk/198877
>
> I think that AS must evaluate the sub query in advance.
>
> It could be possible to have such behavior?
>
>
> Best regards, \ferz
Ferrucio,
The problem is that you are attempting to use a "subquery" to generate
SQL that will be evaluated by the main query. This won't work the same
way that
SELECT (SELECT 'column_name') FROM some_table;
wouldn't work.
If you want to dynamically generate the SQL this way you will have to
create a function or use the DO statement (Postgres 9.0+). It would
look something like this (not tested):
DO $do$
DECLARE
sql text;
output_columns text;
BEGIN
select 'uscita int, ' || substr(descr, 7, length(descr)-8) INTO output_columns
from (select string_agg('" int,"',freephone) as descr
from (select distinct freephone
from calendario order by 1) as a
) as b;
sql := $$select *
FROM crosstab('select uscita,freephone,id from calendario order by
1','select distinct freephone from calendario order by 1')
AS pivot ($$ || output_columns || $$);$$;
EXECUTE sql;
END$do$;
If you are using Postgres <9.0 and don't have access to the DO
statement, you'll have to stick the above into a plpgsql function.
Hope this helps,
--Lee
--
Lee Hachadoorian
PhD, Earth & Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] master/detail
Jan Bakuwel hat am 21. Mai 2012 um 01:17 geschrieben: > Hi, > > I'm trying to get my head around the following question. As an example > take a table with products: > > productid (pk) > name > > and productprice > > productpriceid (pk) > productid (fk) > pricedate > price > > There are multiple records in productprice for each product as prices > (often) go up and (less often) go down. > > I'm looking for a query that returns the following: > > productid, name, pricedate, current_price, difference > > current_price is the latest (ie. most recent date) price of the product > and difference is the difference in price between the latest price and > the price before the latest. > > Any suggestions how to do this with SQL only? I can make it work with a > function (probably less efficient) but think this should be possible > with SQL too... You can use window-function, in your case something like: test=# select * from productprice ; id | product | pricedate | price +-++--- 1 | 1 | 2012-05-01 |10 2 | 1 | 2012-05-05 |15 3 | 1 | 2012-05-10 |12 4 | 1 | 2012-05-15 |22 (4 rows) test=*# select id, product, pricedate, price, lead(price) over (partition by product order by pricedate desc), price - (lead(price) over (partition by product order by pricedate desc)) from productprice; id | product | pricedate | price | lead | ?column? +-++---+--+-- 4 | 1 | 2012-05-15 |22 | 12 | 10 3 | 1 | 2012-05-10 |12 | 15 | -3 2 | 1 | 2012-05-05 |15 | 10 |5 1 | 1 | 2012-05-01 |10 | | (4 rows) Regards, Andreas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] left outer join only select newest record
On Wednesday 23 May 2012 10:46:02 Pavel Stehule wrote: > select distinct on (s.s_registration) * > ... order by u.ud_id desc I tried doing this but it complained about the order by. goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' order by s_stock_no, ud_id desc; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join used_diary u on s.s_regno = u.ud_pex_registration where s_stock_no = 'UL15470' order by ud_id desc; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions goole=# > > or > > select * > from stock_details s > left join (select * from used_diary where (ud_id, > ud_registration) = (select max(ud_id), ud_registration from used_diary > group by ud_registration)) x > on s.s_registration = x.ud_registration; > This was more like what I was thinking, but I still get an error, which I don't understand. I have extracted the inner sub-select and it does only return one record per registration. (The extra criteria is just to ignore old or cancelled tax requests and doesn't affect the query) goole=# select distinct on (s.s_stock_no) s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from stock s left outer join (select ud_id, ud_pex_registration, ud_handover_date from used_diary where (ud_id, ud_pex_registration) = (select max(ud_id), ud_pex_registration from used_diary where (ud_tab is null or ud_tab <> 999) and ud_created > CURRENT_DATE-'4 months'::interval group by ud_pex_registration)) udIn on s.s_regno = udIn.ud_pex_registration; ERROR: more than one row returned by a subquery used as an expression -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Understanding Binary Data Type
On 2012-05-22, Carlos Mennens wrote: > Hello everyone! I wanted to ask the list a question about the 'bytea' > data type & how I can picture this in my head. I've been reading SQL > for about a few months now and since then, I've only been working with > textual data. Basically I'm familiar with storing text and numerical > characters into tables but my friend told me that databases can hold > much more than just ASCI text. In so I've read up on some pages that > describe the bytea data type: > > http://en.wikipedia.org/wiki/Binary_large_object > > http://www.postgresql.org/docs/9.1/static/datatype-binary.html > > So my question is can and in fact does PostgreSQL and most other RDBMS > have the ability to store large binary files like photos, music, etc > etc into an actual table? I'm guessing the data is dumped into the > table but rather linked or parsed through the file system store path > into the database itself, right? I would just like to know in a basic > round about way how databases store and handle large files like .jpg > or .png files & regardless how relative this term is, how common is it > to use these files or 'bytea' data in tables? > > Thanks for any info! postgres has a maximum logical row size just under 2GiB so there's plenty of room there for photos etc (DVD images might not fit). postgres uses a strategy called TOAST to move "wide" values out of the file that holds the main table. implementation detals: http://www.postgresql.org/docs/9.1/static/storage-toast.html -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
