Re: [SQL] CREATE INDEX with order clause
On Wed, 2006-02-01 at 10:46 -0500, Daniel Caune wrote: > Hi, > [snip need for reverse-sort operator class] > > SELECT GAME_CLIENT_VERSION > FROM GSLOG_EVENT > WHERE PLAYER_USERNAME = ? > AND EVENT_NAME = ? > AND EVENT_DATE_CREATED < ? > ORDER BY EVENT_DATE_CREATED DESC > LIMIT 1 > > > Actually, I’m not sure that is useful; perhaps PostgreSQL handles > pretty well such query using an index such as: > CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC > ON GSLOG_EVENT(PLAYER_USERNAME, > EVENT_NAME, > EVENT_DATE_CREATED); > > > Any idea? does index work with: SELECT GAME_CLIENT_VERSION FROM GSLOG_EVENT WHERE PLAYER_USERNAME = ? AND EVENT_NAME = ? AND EVENT_DATE_CREATED < ? ORDER BY PLAYER_USERNAM DESC, EVENT_NAME DESC, EVENT_DATE_CREATED DESC LIMIT 1 gnari ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Help writing a piece of SQL
Hi, I would appreciate some help writing a piece of SQL PG803 My table/data looks like this: username | domain | sendto +-+---+-- Postmaster | intthit08.uk.rabbit.com | root root | intthit08.uk.rabbit.com | [EMAIL PROTECTED] stoat.griffin | trusting.co.uk | [EMAIL PROTECTED] stoat.griffin | trusting.com | [EMAIL PROTECTED] stoat.griffin | rusty.co.uk | [EMAIL PROTECTED] stoat.griffin | rusty.com | [EMAIL PROTECTED] matilda.clematis | trusting.com | [EMAIL PROTECTED] matilda.clematis | trusting.co.uk | [EMAIL PROTECTED] matilda.clematis | rusty.co.uk | [EMAIL PROTECTED] matilda.clematis | rusty.com | [EMAIL PROTECTED] * | trusting.com | [EMAIL PROTECTED] * | trusting.co.uk | [EMAIL PROTECTED] * | rusty.co.uk | [EMAIL PROTECTED] * | rusty.com | [EMAIL PROTECTED] * | windoze.com | [EMAIL PROTECTED] * | windoze.co.uk | [EMAIL PROTECTED] admin | windoze.co.uk | [EMAIL PROTECTED] admin | windoze.com | [EMAIL PROTECTED] * | saxon.co.uk | [EMAIL PROTECTED] * | saxon.com | [EMAIL PROTECTED] The query will have the username and domain passed in as variables. If the username and domain exist then return the sendto The bit I’m struggling with is if the username doesn’t exist then return the sendto where the domain exists e.g. username=fred (this doesn’t exist) and domain=rusty.com then return [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Any help on this would very much appreciated; it’s been driving me mad for the last day. Thanks Nigel Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes.Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation.ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved.
Re: [SQL] Help writing a piece of SQL
Nigel Bishop wrote: username |domain |sendto +-+---+- - Postmaster | intthit08.uk.rabbit.com | root root | intthit08.uk.rabbit.com | [EMAIL PROTECTED] stoat.griffin| trusting.co.uk| [EMAIL PROTECTED] stoat.griffin| trusting.com | [EMAIL PROTECTED] stoat.griffin| rusty.co.uk | [EMAIL PROTECTED] stoat.griffin| rusty.com | [EMAIL PROTECTED] *| trusting.com | [EMAIL PROTECTED] *| trusting.co.uk| [EMAIL PROTECTED] *| rusty.co.uk | [EMAIL PROTECTED] *| rusty.com | [EMAIL PROTECTED] The query will have the username and domain passed in as variables. If the username and domain exist then return the sendto The bit I'm struggling with is if the username doesn't exist then return the sendto where the domain exists e.g. username=fred (this doesn't exist) and domain=rusty.com then return [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] So: if the username doesn't exist then you return ALL rows with a matching domain? Not just username="*"? I'm guessing I've mis-understood and you just want username="*", which would be something like this: SELECT sendto, 1 AS priority FROM tbl WHERE username=$1 AND domain=$2 UNION ALL SELECT sendto, 2 AS priority FROM tbl WHERE username='*' AND domain=$2 ORDER BY priority LIMIT 1; Does that help at all? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help writing a piece of SQL
Thanks for that Richard, it's almost what I'm after: So where the un and dm exist then return 1 row Where the un doestn't exist and dm does then return all sendtos' where the dm exists not just where the un='*' I hope that makes sense. Thanks for you help Nigel Bishop ioko T: +44 (0)1904 435 458 M: +44 (0)7881 624 386 F: +44 (0)1904 435 450 E: [EMAIL PROTECTED] W: www.ioko.com -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: 03 February 2006 11:02 To: Nigel Bishop Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Help writing a piece of SQL Nigel Bishop wrote: > > username |domain |sendto > +-+---+- > - > > Postmaster | intthit08.uk.rabbit.com | root > root | intthit08.uk.rabbit.com | [EMAIL PROTECTED] > stoat.griffin| trusting.co.uk| [EMAIL PROTECTED] > stoat.griffin| trusting.com | [EMAIL PROTECTED] > stoat.griffin| rusty.co.uk | [EMAIL PROTECTED] > stoat.griffin| rusty.com | [EMAIL PROTECTED] > *| trusting.com | [EMAIL PROTECTED] > *| trusting.co.uk| [EMAIL PROTECTED] > *| rusty.co.uk | [EMAIL PROTECTED] > *| rusty.com | [EMAIL PROTECTED] > The query will have the username and domain passed in as variables. > If the username and domain exist then return the sendto > The bit I'm struggling with is if the username doesn't exist then return > the sendto where the domain exists > > e.g. username=fred (this doesn't exist) and domain=rusty.com then > return [EMAIL PROTECTED], [EMAIL PROTECTED], > [EMAIL PROTECTED] So: if the username doesn't exist then you return ALL rows with a matching domain? Not just username="*"? I'm guessing I've mis-understood and you just want username="*", which would be something like this: SELECT sendto, 1 AS priority FROM tbl WHERE username=$1 AND domain=$2 UNION ALL SELECT sendto, 2 AS priority FROM tbl WHERE username='*' AND domain=$2 ORDER BY priority LIMIT 1; Does that help at all? -- Richard Huxton Archonet Ltd Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes. Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation. ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved. ---(end of broadcast)--- TIP 1: 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] Help writing a piece of SQL
On 3 feb 2006, at 11.43, Nigel Bishop wrote: The query will have the username and domain passed in as variables. If the username and domain exist then return the sendto The bit I’m struggling with is if the username doesn’t exist then return the sendto where the domain exists e.g. username=fred (this doesn’t exist) and domain=rusty.com then [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] I think this could do the trick for you: SELECT sendto FROM users t1 WHERE domain='rusty.com' AND (username='fred') = EXISTS(SELECT 1 FROM users t2 WHERE username='fred' and domain=t1.domain); Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help writing a piece of SQL
Niklas, Thank you very much, that did the business Cheers Nigel Bishop -Original Message- From: Niklas Johansson [mailto:[EMAIL PROTECTED] Sent: 03 February 2006 13:00 To: Nigel Bishop Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Help writing a piece of SQL On 3 feb 2006, at 11.43, Nigel Bishop wrote: > The query will have the username and domain passed in as variables. > If the username and domain exist then return the sendto > The bit I'm struggling with is if the username doesn't exist then > return the sendto where the domain exists > > e.g. username=fred (this doesn't exist) and domain=rusty.com then > [EMAIL PROTECTED], [EMAIL PROTECTED], > [EMAIL PROTECTED] I think this could do the trick for you: SELECT sendto FROM users t1 WHERE domain='rusty.com' AND (username='fred') = EXISTS(SELECT 1 FROM users t2 WHERE username='fred' and domain=t1.domain); Sincerely, Niklas Johansson Communications on or through ioko's computer systems may be monitored or recorded to secure effective system operation and for other lawful purposes. Unless otherwise agreed expressly in writing, this communication is to be treated as confidential and the information in it may not be used or disclosed except for the purpose for which it has been sent. If you have reason to believe that you are not the intended recipient of this communication, please contact the sender immediately. No employee is authorised to conclude any binding agreement on behalf of ioko with another party by e-mail without prior express written confirmation. ioko365 Ltd. VAT reg 656 2443 31. Reg no 3048367. All rights reserved. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help writing a piece of SQL
On 3 feb 2006, at 14.06, Nigel Bishop wrote: Thank you very much, that did the business This should generate the same plan as the previous query, but be a little bit more clean and easy to read: SELECT sendto FROM users t1 WHERE domain='rusty.com' AND username = COALESCE((SELECT username FROM users WHERE username='fred' and domain=t1.domain), username); Sincerely, Niklas Johansson ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
