[SQL] How to recognize trigger-inserted rows?
I have a table with an INSERT/UPDATE/DELETE statement trigger. The trigger's action is to insert "jobs" into a queue noting that the table has changed. A number of other tables have FK relationships with this table, and they have their own statement triggers that fire on DELETE. When I delete a number of rows from the first table, the cascading deletes into the other tables generate a rather large number of trigger-fires, so I end up with way too many rows in the queue-table. What I would like to do is, within the transaction doing the top-level delete, examine the queue-table for duplicate rows and remove those, since they are extraneous. Ideally I would look for rows that have the same transaction ID, but I'm having trouble determining what the current ID is. I'm using Pg 7.4. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How to re-sort a sorted query?
I have a query that need to be sorted in order of price of store's product with unique store number. Here is a sample data of storeproduct table: ItemSku , StoreNumber , Price == 10001 , 7 , 30.00 10001 , 7 , 35.00 <-- duplicate store number 10001 , 5 , 45.00 10001 , 2 , 50.00 Then I do this query to get unique store number and also the cheapest price from each store: SQL= "Select distinct on (storenumber), itemsku, storenumber,price from storeproduct where itemsku='10001' order by storenumber, price" Result #1: ItemSku , StoreNumber , Price 10001 , 2 , 50.00 10001 , 5 , 45.00 10001 , 7 , 30.00 The question is how to make the query that returns as above but sorted by price? Thanks.. Yudie
[SQL] How do you compare (NULL) and (non-NULL)?
In the following query SELECT Parent FROM Channels ORDER BY Parent ASC; If I have a couple of (NULL)s in the field [Parent], they will be listed at the bottom of the query result. Is it because PostgreSQL considers (NULL) as the biggest value? If I run the same query under MSSQL Server 2000, I get the exact opposite result regarding the order of (NULL)s and (non-NULL) values. They are listed at the very beginning of the query result. Thanks Wei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to re-sort a sorted query?
On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote: > I have a query that need to be sorted in order of price of store's > product with unique store number. > > Here is a sample data of storeproduct table: > > > ItemSku , StoreNumber , Price > == > 10001 , 7 , 30.00 > 10001 , 7 , 35.00 <-- duplicate store number > 10001 , 5 , 45.00 > 10001 , 2 , 50.00 > > Then I do this query to get unique store number and also the cheapest > price from each store: > > SQL= "Select distinct on (storenumber), itemsku, storenumber,price > from storeproduct where itemsku='10001' > order by storenumber, price" That won't get you the cheapest price, just an arbitrary one determined by the physical storage order. You need to use GROUP BY with an aggregate function: SELECT itemsku, storenumber, MIN(price) FROM storeproduct WHERE itemsku = '10001' GROUP BY itemsku, storenumber ORDER BY price, storenumber; > Result #1: > ItemSku , StoreNumber , Price > 10001 , 2 , 50.00 > 10001 , 5 , 45.00 > 10001 , 7 , 30.00 > > The question is how to make the query that returns as above but sorted > by price? The literal answer to your question is to put price first in the ORDER BY clause, but I'm not convinced you actually want to know something that simple. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How do you compare (NULL) and (non-NULL)?
On Tue, 26 Oct 2004, Wei Weng wrote: > In the following query > > SELECT Parent FROM Channels ORDER BY Parent ASC; > > If I have a couple of (NULL)s in the field [Parent], they will be listed at > the bottom of the query result. > > Is it because PostgreSQL considers (NULL) as the biggest value? If I run the > same query under MSSQL Server 2000, I get the exact opposite result > regarding the order of (NULL)s and (non-NULL) values. They are listed at the > very beginning of the query result. The spec basically says (IIRC) that implementations must either treat all nulls as greater than all non-nulls for ordering or less than all non-nulls for ordering, but that different implementations may choose different choices. I think the most recent version (at least) provides an option to specify which way to handle nulls, but we don't support that as far as I know. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How do you compare (NULL) and (non-NULL)?
On Tue, Oct 26, 2004 at 01:48:48PM -0700, Stephan Szabo wrote: > On Tue, 26 Oct 2004, Wei Weng wrote: > > > In the following query > > > > SELECT Parent FROM Channels ORDER BY Parent ASC; > > > > If I have a couple of (NULL)s in the field [Parent], they will be listed at > > the bottom of the query result. > > > > Is it because PostgreSQL considers (NULL) as the biggest value? If I run the > > same query under MSSQL Server 2000, I get the exact opposite result > > regarding the order of (NULL)s and (non-NULL) values. They are listed at the > > very beginning of the query result. you could try to use COALESCE to treat NULLs as either a minimal or maximal value so that your ordering is correct : SELECT Parent FROM Channels ORDER BY COALESCE(Parent, -1) ASC; to treat NULLs as -1 for example hth Jerome Alet ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to re-sort a sorted query?
Oliver ,sorry, I didn't submit all complete fields as my example, The reason I didn't use agregate function because I still need to select another fields from storeproduct table and some outer joins. What about if the data in storeproduct table shows like this: ItemSku , StoreNumber , Price, Condition == 10001 , 7 , 30.00, Used 10001 , 7 , 35.00, New <-- duplicate store number 10001 , 5 , 45.00, New 10001 , 2 , 50.00, New However, should I use temporary table to make it simple? what about the performance? Yudie - Original Message - From: "Oliver Elphick" <[EMAIL PROTECTED]> To: "Yudie" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, October 26, 2004 3:34 PM Subject: Re: [SQL] How to re-sort a sorted query? On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote: > I have a query that need to be sorted in order of price of store's > product with unique store number. > > Here is a sample data of storeproduct table: > > > ItemSku , StoreNumber , Price > == > 10001 , 7 , 30.00 > 10001 , 7 , 35.00 <-- duplicate store number > 10001 , 5 , 45.00 > 10001 , 2 , 50.00 > > Then I do this query to get unique store number and also the cheapest > price from each store: > > SQL= "Select distinct on (storenumber), itemsku, storenumber,price > from storeproduct where itemsku='10001' > order by storenumber, price" That won't get you the cheapest price, just an arbitrary one determined by the physical storage order. You need to use GROUP BY with an aggregate function: SELECT itemsku, storenumber, MIN(price) FROM storeproduct WHERE itemsku = '10001' GROUP BY itemsku, storenumber ORDER BY price, storenumber; > Result #1: > ItemSku , StoreNumber , Price > 10001 , 2 , 50.00 > 10001 , 5 , 45.00 > 10001 , 7 , 30.00 > > The question is how to make the query that returns as above but sorted > by price? The literal answer to your question is to put price first in the ORDER BY clause, but I'm not convinced you actually want to know something that simple. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] How to re-sort a sorted query?
On Tue, 2004-10-26 at 17:43 -0400, Tom Lane wrote: > Oliver Elphick <[EMAIL PROTECTED]> writes: > > On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote: > >> Then I do this query to get unique store number and also the cheapest > >> price from each store: > >> > >> SQL= "Select distinct on (storenumber), itemsku, storenumber,price > >> from storeproduct where itemsku='10001' > >> order by storenumber, price" > > > That won't get you the cheapest price, > > Sure it will. It's a perfectly good application of DISTINCT ON. > However, he has to use that particular ORDER BY to get the answers > he wants. Ah - because ORDER BY is applied before DISTINCT ON; I hadn't realised that. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Whosoever therefore shall be ashamed of me and of my words in this adulterous and sinful generation; of him also shall the Son of man be ashamed, when he cometh in the glory of his Father with the holy angels." Mark 8:38 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] How to re-sort a sorted query?
Oliver Elphick <[EMAIL PROTECTED]> writes: > On Tue, 2004-10-26 at 14:23 -0500, Yudie wrote: >> Then I do this query to get unique store number and also the cheapest >> price from each store: >> >> SQL= "Select distinct on (storenumber), itemsku, storenumber,price >> from storeproduct where itemsku='10001' >> order by storenumber, price" > That won't get you the cheapest price, Sure it will. It's a perfectly good application of DISTINCT ON. However, he has to use that particular ORDER BY to get the answers he wants. So the only way (I think) to change the ordering for display is to wrap this as a sub-select: select * from (select distinct on (storenumber), itemsku, storenumber,price from storeproduct where itemsku='10001' order by storenumber, price) ss order by price; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
