Re: [SQL] Order by YYYY MM DD in reverse chrono order trouble
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I am trying to select distinct dates and order them in the reverse > chronological order. Although the column type is TIMESTAMP, in this > case I want only , MM, and DD back. If you don't need them separated (which I suspect may be the case), you can do something like this: SELECT DISTINCT TO_CHAR(add_date,'-MM-DD') AS bb FROM tt ORDER BY bb DESC; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200404240716 -BEGIN PGP SIGNATURE- iD8DBQFAikzOvJuQZxSWSsgRAgqbAKDC75SQd2aExYaniSJIzovOlVjvCACgyOAl Q2KMp3YGBkQwy5y4h9r/96A= =4idZ -END PGP SIGNATURE- ---(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] Design Problem...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > The problem is that it is possible that the service can be switched to a > different business unit, and then possibly back to the original later on. First, you will get more responses if you do not create a new topic in the middle of an existing thread. Here is a simplified answer to your problem. Basically, you need to create a link between a business and a service, and note when that link was created. You can grab the highest creation time for a service to see which business currently owns it. Depending on how often things change around, you may want to simply have a trigger on the bs_map table that updates a "business" column in the services table, rather than having to compute the max creation time constantly. CREATE TABLE business ( idSERIAL UNIQUE, bname VARCHAR ); CREATE TABLE service ( idSERIAL UNIQUE, sname VARCHAR ); CREATE TABLE bs_map ( business INTEGER NOT NULL, service INTEGER NOT NULL, assigned TIMESTAMPTZ NOT NULL DEFAULT now() ); ALTER TABLE bs_map ADD CONSTRAINT "bs_map_business_fk" FOREIGN KEY (business) REFERENCES business(id) ON DELETE RESTRICT ON UPDATE CASCADE; ALTER TABLE bs_map ADD CONSTRAINT "bs_map_service_fk" FOREIGN KEY (service) REFERENCES service(id) ON DELETE RESTRICT ON UPDATE CASCADE; - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200404241255 -BEGIN PGP SIGNATURE- iD8DBQFAipwPvJuQZxSWSsgRApPVAJwPvc2aTadzTfKBJIge+2bh+IQ7qwCeN2rZ MTilGUtbg0y4DOAENUzXc80= =Jw5D -END PGP SIGNATURE- ---(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] Question re: serial
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have a table with a primary key defined as serial unique. When I insert a row into the table, I need to get the value of that row back so that I can use it to link rows in another table to it. How do I do that in Postgresql? I know that I can define a sequence and insert the value manually, but is there a way to query the value of the most recently inserted serial column of a row? -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) iD8DBQFAiqK0jeziQOokQnARArJKAJ0SEs8DO6ETa6yZI2Wq6SOmydEwzQCeIwLF on3NovbSaHa+tBrvrynyQfI= =d0AB -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Question re: serial
On Sat, Apr 24, 2004 at 12:24:03 -0500, Michael Satterwhite <[EMAIL PROTECTED]> wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > I have a table with a primary key defined as serial unique. When I insert a > row into the table, I need to get the value of that row back so that I can > use it to link rows in another table to it. How do I do that in Postgresql? I > know that I can define a sequence and insert the value manually, but is there > a way to query the value of the most recently inserted serial column of a > row? When you use a serial type there a sequence is created for you. You can reference the most recent value of that sequence within the current session using currval. You can use the \d command in psql to see what sequence is being used to calculate the default value. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Question re: serial
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sat, 24 Apr 2004, Michael Satterwhite wrote: > I have a table with a primary key defined as serial unique. When I insert a > row into the table, I need to get the value of that row back so that I can > use it to link rows in another table to it. How do I do that in Postgresql? I > know that I can define a sequence and insert the value manually, but is there > a way to query the value of the most recently inserted serial column of a > row? See currval() and nextval() functions: Regards, - -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.TDMSoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFAiqXrtl86P3SPfQ4RAsxkAKCQ9oL9Jo4a7VZwqyI4YU6L7i3nEACgv/wM 0ce7up3AVctCYyksk7OYIpY= =8Hr5 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
