Re: [SQL] Hey! ORDER BY in VIEWS?
Josh Berkus wrote: > > Pater, Robbie, Bruce, > > Makes sense. I take it that this is a deviation from the ANSI 92 > standard, then? > > What happens if I put an ORDER BY in a view, then call an ORDER BY in a > query, e.g.: > Does the second ORDER BY override or suppliment the view ORDER BY, or is > it ignored? It overrides. People seem to be forgetting ORDER BY ... LIMIT has selective qualities as well as ordering ones. The example someone gave me was when you use LIMIT ... OFFSET to fetch results a page at a time. If you want the last page of your results you need to do something like: SELECT * FROM messages ORDER BY msg_timestamp DESC LIMIT 20; But - this gives them in reverse timestamp order. So - wrap the query in a view and then apply your own ORDER BY. Can't remember who came up with this (some evil genius :-) - but it seemed to make sense so I stuck the example in my PostgreSQL notes. - Richard Huxton ---(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
[SQL] SELECT * from select - HOW?
hi, how can we select * from a view named 'select'?? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] SELECT * FROM select -- got it!
sorry! infact, i didn't try that with quote. now it is fine.. sorry for ur inconvinence and thankx however. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] is it possible to comment each column of a table?
hi all, is it possible to comment a column of a table similar to EXTRA of mySQL. If yes then how?? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] interpreting attributes in pg_class
Hi,
I need to find the entries for "my" tables within pg_class, without the
indexes ("..._pkey").
How does one have to interpret "reltype" and that, and where are the
docs/header files?
Thanks,
Markus
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Hey! ORDER BY in VIEWS?
> But - this gives them in reverse timestamp order. So - wrap the query in > a view and then apply your own ORDER BY. > > Can't remember who came up with this (some evil genius :-) - but it > seemed to make sense so I stuck the example in my PostgreSQL notes. We kept rejecting the idea of ORDER BY in a view until someone suggested LIMIT and ORDER BY, at which point we had to enable it. I think Tom did the work. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] SQL - histogram
One way to do this is to use the 'CASE' expression, documented here: http://pgsql.dbexperts.com.br/devel-corner/docs/postgres/functions-conditional.html Because you have only one record per person, you can use the 'COUNT' aggregate function to see how many of each height category match. An example SQL statement for your height analysis would be: SELECT CASE WHEN height < 150 THEN '< 150' WHEN height BETWEEN 150 AND 160 THEN '150 - 160' WHEN height BETWEEN 160 AND 170 THEN '160 - 170' WHEN height BETWEEN 170 AND 180 THEN '170 - 180' WHEN height BETWEEN 180 AND 190 THEN '180 - 190' WHEN height BETWEEN 190 AND 200 THEN '190 - 200' ELSE '> 200' END AS category, COUNT(*) AS qty FROM person_table GROUP BY category; On 9 Jul 2001, Txugo wrote: > Hi, > I've a problem as follow: > I have a table where one record represent a person, including his height. > I'd like to know how many person have more than 150 cm, more than 160 cm > and so on. > How can I do that using SQL? > > Example: > people > 150 - 1000 >> 160 - 850 >> 170 - 500 >> 180 - 200 >> 190 - 30 > thanks in advance ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: "Display of specified number of records."
Use "<", ">" and "AND" after the "WHERE" to filter your return. This works only if your testID is Serialized (ie 1,2,3). If they are uniqueID's, another field must be used in it's place ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Help with SQL statement - Thanks
SELECT * FROM Payments P1 WHERE Date = (SELECT MAX(Date) FROM Payments P2 WHERE P2.CustomerNo = P1.CustomerNo) I think that will do it. - James - Original Message - From: "Henry" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]@postgresql.org> Sent: Saturday, July 14, 2001 3:16 PM Subject: [SQL] Help with SQL statement - Thanks > There is a table called "Payments" which records every payment that a > company has received: > > CustomerNo DateAmount > > 32142/1230 > 32144/1050 > 99434/1840 > 99435/1030 > 99432/1370 > 11215/2310 > 11214/2040 > 11213/1230 > (more data...) > > > I want to be able to pull out the last payment made by everyone in a query: > > CustomerNo DateAmount > --- > 32144/1050 > 99435/1030 > 11215/2310 > (other users...) > > How should I write the SQL statement? Please email to me at > [EMAIL PROTECTED] Thank you very much. > > Henry > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] interpreting attributes in pg_class
Markus Wagner writes:
> I need to find the entries for "my" tables within pg_class, without the
> indexes ("..._pkey").
> How does one have to interpret "reltype" and that, and where are the
> docs/header files?
http://www.de.postgresql.org/users-lounge/docs/7.1/postgres/catalog-pg-class.html
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] ERROR: UNIQUE constraint matching given keys for referenced table "sequences" not found
I'm trying to create some tables with foreign keys. When I try to create a foreign key ... foreign key(seq_code) references sequences(seq_code) on update CASCADE on delete CASCADE, I get this message: ERROR: UNIQUE constraint matching given keys for referenced table "sequences" not found The problem is that the referenced field and table exist. Any hint? Using psql and PostgreSQL 7.1.1 on RedHat 7.1 Thanks. -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 553-8000 x??? [EMAIL PROTECTED] San Francisco, CA. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
