Re: [SQL] Re: Restriction by grouping problem.
Philip Warner <[EMAIL PROTECTED]> writes: > This will work, but will not solve the larger problem of 'give me all the > attrs of of the record with the least datetime for a given SID'. Jeff does > not need this, but it is a common problem. Simply using min/max works for > one attr, but fails to provide a consistent view of multiple attrs. PG has > no elegant single-statement solution to this problem. Yes it does: SELECT DISTINCT ON (sid) * FROM logs ORDER BY sid, datetime; This gives you just one output row per distinct "sid" value, and that row will be the one with least datetime. See the DISTINCT ON example in the SELECT reference page. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Who do I make _ not a wildcard?
I also think two underscores make a literal underscore. Same with %. > On Sat, 28 Jul 2001, Roy Souther wrote: > > > In PG the _ is a wildcard that means any singal char. I need to do a search > > for the actual _ char and not get back thousands of wrong matches. Is there > > and escape char that I could use? This needs to work with PG 7.0.3 & 7.1.2. > > \\_ should work for a literal escape. > At least on current sources you can do something like: > like 'blah!_%' escape '!' > where ! becomes the escape character for the string. > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Date Time Functions - ANSI SQL ?
Curious to know... Are all the date time functions described in the pgSQL docs are ANSI-SQL or pgSQL extensions? For Example in the Docs: 4.7.1. EXTRACT, date_part EXTRACT (field FROM source) The extract function retrieves sub-fields from date/time values, such as year or hour. source is a value expression that evaluates to type timestamp or interval. (Expressions of type date or time will be cast to timestamp and can therefore be used as well.) field is an identifier (not a string!) that selects what field to extract from the source value. The extract function returns values of type double precision. The following are valid values: day The day (of the month) field (1 - 31) SELECT EXTRACT(DAY FROM TIMESTAMP '2001-02-16 20:38:40'); Result: 16 Thanks all, ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Date Time Functions - ANSI SQL ?
Gonzo Rock <[EMAIL PROTECTED]> writes: > Are all the date time functions described in the pgSQL docs are > ANSI-SQL or pgSQL extensions? One or the other, yes ;-) EXTRACT() is ANSI, though I think we may accept a few more field keywords than the spec mentions. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
