[SQL] Question about One to Many relationships
Hi, This should be a simple idea, but I've been going back and forth on it with various people in my tech group. So I've got two tables, one for albums and one for bands, for simplicity's sake, they look like this: CREATE TABLE bands ( id serial PRIMARY KEY, name varchar(64) NOT NULL CHECK( name <> ''), UNIQUE(name) ); CREATE TABLE albums ( id serial PRIMARY KEY, name varchar(128) NOT NULL CHECK( name <> '') ); And I want to link the band to the album, but, if the album is a compilation it'll be linked to multiple band.ids, so i can't just add a column like: band_id integer REFERENCES band (id) to the albums table, othewise i'd have to duplicate the albums in the table (one record for each band associated with an album). I thought a lookup table would be appropriate here, so like: CREATE TABLE bands_on_album ( id serial PRIMARY KEY, band_id integer REFERENCES band (id), album_id integer REFERENCES albums (id) ) but i'm being told this is "wrong" I feel like this is the accurate way to do this, does any one have experience on this matter? Thanks! Todd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about One to Many relationships
On Fri, 24 Mar 2006 11:52:31 -0500 "Todd Kennedy" <[EMAIL PROTECTED]> wrote: > So I've got two tables, one for albums and one for bands, for > simplicity's sake, they look like this: > > CREATE TABLE bands ( > id serial PRIMARY KEY, > name varchar(64) NOT NULL CHECK( name <> ''), > UNIQUE(name) > ); > > CREATE TABLE albums ( > id serial PRIMARY KEY, > name varchar(128) NOT NULL CHECK( name <> '') > ); > > And I want to link the band to the album, but, if the album is a > compilation it'll be linked to multiple band.ids, so i can't just add > a column like: > > band_id integer REFERENCES band (id) > > to the albums table, othewise i'd have to duplicate the albums in the > table (one record for each band associated with an album). > > I thought a lookup table would be appropriate here, so like: > > CREATE TABLE bands_on_album ( > id serial PRIMARY KEY, > band_id integer REFERENCES band (id), > album_id integer REFERENCES albums (id) > ) > > but i'm being told this is "wrong" Wrong in what sense? I can see issues depending on what your requirements are. Well, one issue. There is nothing in the above definition that guarantees that every album has at least one band on it. Is that an issue in this system? Otherwise, I can't see anything wrong from a relational database POV. What are people saying is wrong about it? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] Question about One to Many relationships
Child table references a not-existing table:
('band' insted of 'bands')
Error: ERROR: relation "band" does not exist
> CREATE TABLE bands (
> CREATE TABLE bands_on_album (
- - -
> band_id integer REFERENCES band (id),
- - -
Regards,
Milorad Poluga
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] Question about One to Many relationships
And I want to link the band to the album, but, if the album is a compilation it'll be linked to multiple band.ids, so i can't just add a column like: For a compilation, you should link a band to a track, not an album. This opens another can of worms... I would use the following tables : CREATE TABLE albums ( id SERIAL PRIMARY KEY ... other data ); CREATE TABLE tracks ( id SERIAL PRIMARY KEY album_id INTEGER NOT NULL REFERENCES albums( id ) ... other data ): CREATE TABLE artists ( id SERIAL PRIMARY KEY ... other data ); -- A role is : composer, performer, singer, DJ, compilation maker, lead violonist, etc. CREATE TABLE roles ( id SERIAL PRIMARY KEY ... other data ); CREATE TABLE track_artists ( track_id INTEGER NOT NULL REFERENCES tracks( id ) role_id INTEGER NOT NULL REFERENCES roles( id ) artist_id INTEGER NOT NULL REFERENCES artists( id ) ); And you may also with to specify main artists for an album : CREATE TABLE track_artists ( album_id INTEGER NOT NULL REFERENCES albums( id ) role_id INTEGER NOT NULL REFERENCES roles( id ) artist_id INTEGER NOT NULL REFERENCES artists( id ) ); You will then need a few LEFT JOINs and to get the artists for a track. It is interesting to know if the artist comes from the album or from the track. For instance the composer of the whole album might invite a guest singer on some tracks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Question about One to Many relationships
They haven't responded me as of yet. There should be a band associated with each album -- this is handled in code, but other than that this is the only relational db way I can think of to do it. Thanks! Todd On 3/24/06, D'Arcy J.M. Cain wrote: > On Fri, 24 Mar 2006 11:52:31 -0500 > "Todd Kennedy" <[EMAIL PROTECTED]> wrote: > > So I've got two tables, one for albums and one for bands, for > > simplicity's sake, they look like this: > > > > CREATE TABLE bands ( > > id serial PRIMARY KEY, > > name varchar(64) NOT NULL CHECK( name <> ''), > > UNIQUE(name) > > ); > > > > CREATE TABLE albums ( > > id serial PRIMARY KEY, > > name varchar(128) NOT NULL CHECK( name <> '') > > ); > > > > And I want to link the band to the album, but, if the album is a > > compilation it'll be linked to multiple band.ids, so i can't just add > > a column like: > > > > band_id integer REFERENCES band (id) > > > > to the albums table, othewise i'd have to duplicate the albums in the > > table (one record for each band associated with an album). > > > > I thought a lookup table would be appropriate here, so like: > > > > CREATE TABLE bands_on_album ( > > id serial PRIMARY KEY, > > band_id integer REFERENCES band (id), > > album_id integer REFERENCES albums (id) > > ) > > > > but i'm being told this is "wrong" > > Wrong in what sense? I can see issues depending on what your > requirements are. Well, one issue. There is nothing in the above > definition that guarantees that every album has at least one band on > it. Is that an issue in this system? Otherwise, I can't see anything > wrong from a relational database POV. > > What are people saying is wrong about it? > > -- > D'Arcy J.M. Cain | Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about One to Many relationships
We're not concerned with the track info. This is a listing of album information, hence the one to many relationship between the album and the artist. and for the record, i should correct myself. he said it was "bad" not "wrong". but i hadn't given him all the details. But. Yes. Thank you all for your help. On 3/24/06, Joe <[EMAIL PROTECTED]> wrote: > Todd Kennedy wrote: > > They haven't responded me as of yet. There should be a band associated > > with each album -- this is handled in code, but other than that this > > is the only relational db way I can think of to do it. > > But if a band can have songs in many albums and an album can have songs > from multiple bands, it's a many-to-many relationship, NOT one-to-many. > Short of the full track design suggested by PFC, you'd normally > implement a many-to-many table as follows: > > CREATE TABLE bands_on_album ( > band_id integer REFERENCES band (id), > album_id integer REFERENCES albums (id), > PRIMARY KEY (band_id, album_id) > ) > > This of course precludes the same band being listed twice in a given > album. If you do need that info, then you're really asking for "tracks". > > Joe > > ---(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
Re: [SQL] Question about One to Many relationships
On Fri, 24 Mar 2006 13:34:34 -0500 Joe <[EMAIL PROTECTED]> wrote: > Todd Kennedy wrote: > > They haven't responded me as of yet. There should be a band associated > > with each album -- this is handled in code, but other than that this > > is the only relational db way I can think of to do it. > > But if a band can have songs in many albums and an album can have songs > from multiple bands, it's a many-to-many relationship, NOT one-to-many. > Short of the full track design suggested by PFC, you'd normally > implement a many-to-many table as follows: > > CREATE TABLE bands_on_album ( > band_id integer REFERENCES band (id), > album_id integer REFERENCES albums (id), > PRIMARY KEY (band_id, album_id) > ) > > This of course precludes the same band being listed twice in a given > album. If you do need that info, then you're really asking for "tracks". I think that you are making assumptions about his requirements. Also, who's to say that each track can have one and only one band? What if you have an album of sound effects? That's no bands. I think that the most instructive thing would be to hear what his friends claim the problem is. Their issue may be based on a more complete knowledge of his requirements. That may be more complicated, simpler or both than we know now. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about One to Many relationships
Todd Kennedy wrote: They haven't responded me as of yet. There should be a band associated with each album -- this is handled in code, but other than that this is the only relational db way I can think of to do it. But if a band can have songs in many albums and an album can have songs from multiple bands, it's a many-to-many relationship, NOT one-to-many. Short of the full track design suggested by PFC, you'd normally implement a many-to-many table as follows: CREATE TABLE bands_on_album ( band_id integer REFERENCES band (id), album_id integer REFERENCES albums (id), PRIMARY KEY (band_id, album_id) ) This of course precludes the same band being listed twice in a given album. If you do need that info, then you're really asking for "tracks". Joe ---(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
Re: [SQL] Custom type
On Wed, 22 Mar 2006 13:07:33 -0800 Bryce Nesbitt <[EMAIL PROTECTED]> threw this fish to the penguins: > Terry Lee Tucker wrote: > > rnd=# \h comment > > Command: COMMENT > > Description: define or change the comment of an object > > ..I believe this is what you need. > > > Cool! > That's a great feature. Though it would be even nicer if the comment > showed when > you "\d" a table:: Hmm, it does(in 8.1.0) if you just say "\d+" without an argument: newschm3=# comment on table fffg is 'the rain'; newschm3=# \d+ List of relations Schema | Name | Type | Owner | Description +--+---+--+- public | fffg | table | postgres | the rain public | group_run_limit | table | postgres | public | hold_log | table | postgres | ... But not if you specify the object: newschm3=# \d+ fffg Table "public.fffg" Column | Type | Modifiers | Description +-+---+- t | text| | i | integer | | This seems a bit like a bug to me... -- George Young > stage=# comment on table db_version is 'Managed in nautilus.xml'; > stage=# \d db_version > > Table "public.db_version" > Column | Type | Modifiers > -+---+--- > version | character varying(64) | > > > stage=# \dd db_version > > Object descriptions > Schema |Name| Object | Description > +++- > public | db_version | table | Managed in nautilus.xml > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Find min and max values across two columns?
Hello. I've recently begun to use PostgreSQL in earnest (working with data as opposed to just having clever applications tuck it away in there) and have hit a wall with something. I'm trying to build a query that among other things, returns the minimum and maximum values contained in either of two columns. The problem is that min() and max() only take one column name as an argument. Is there some clever way that I can craft an expression to find the min/max across both columns? I have yet to delve into any pgsql so if it requires that, then please go easy on me. :) Example: id | columnA | columnB 1 | 4 | 2 2 | 3 | 4 2 | 5 | 1 I'd like to be able to discover that 1 is the smallest value in either columnA or columnB and 5 is the largest. I don't actually care what rows they are in, I just want the values. Thanks for your time! Any help or pointers to relevant reading material on this would be greatly appreciated. (I have been using the excellent PostgreSQL docs and an equally good book titled Beginning Databases with PostgreSQL by Neil Matthew and Richard Stones so far.) -- Amos ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] generate_series to return row that doesn't exist in table...
Hi, I have a table wich contains aggregated data, table stats_activity logtime timestamptz, count int given this dataset "2006-03-24 03:00:00+01";55 "2006-03-24 04:00:00+01";33 "2006-03-24 06:00:00+01";46 "2006-03-24 07:00:00+01";63 "2006-03-24 08:00:00+01";88 I want to get this in order to plot the data "2006-03-24 03:00:00+01";55 "2006-03-24 04:00:00+01";33 >>"2006-03-24 05:00:00+01";0<< "2006-03-24 06:00:00+01";46 "2006-03-24 07:00:00+01";63 "2006-03-24 08:00:00+01";88 I used generate_series to get all the timestamps I need but I don't know how to write my query. I've tried various combination of subselects, joins, union,... and I never managed to get the result I wanted... I'm sure the solution is trivial but I don't get it... I prefer to generate missing rows "on the fly" intead of actually storing useless data on the table. Thanks for your help, -- MaXX ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] generate_series to return row that doesn't exist in
On Fri, 2006-03-24 at 14:30, MaXX wrote: > Hi, > > I have a table wich contains aggregated data, > table stats_activity > logtime timestamptz, > count int > > given this dataset > "2006-03-24 03:00:00+01";55 > "2006-03-24 04:00:00+01";33 > "2006-03-24 06:00:00+01";46 > "2006-03-24 07:00:00+01";63 > "2006-03-24 08:00:00+01";88 > > I want to get this in order to plot the data > "2006-03-24 03:00:00+01";55 > "2006-03-24 04:00:00+01";33 > >>"2006-03-24 05:00:00+01";0<< > "2006-03-24 06:00:00+01";46 > "2006-03-24 07:00:00+01";63 > "2006-03-24 08:00:00+01";88 > > I used generate_series to get all the timestamps I need but I don't know how > to write my query. > > > I've tried various combination of subselects, joins, union,... and I never > managed to get the result I wanted... I'm sure the solution is trivial but I > don't get it... > I prefer to generate missing rows "on the fly" intead of actually storing > useless data on the table. More than likely you need a left join and a case statement. select , case when a.date is null then 0 else a.date end from (select * from generate_series() -- magic to get dates goes here) as p left join maintable as a on (p.date=a.date); There may be some small syntax error in there, as I've not tested it. The relavent pages are: case: http://www.postgresql.org/docs/8.1/static/functions-conditional.html joins: http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Find min and max values across two columns?
On Fri, 2006-03-24 at 15:19 -0500, Amos Hayes wrote: > Hello. I've recently begun to use PostgreSQL in earnest (working with > data as opposed to just having clever applications tuck it away in > there) and have hit a wall with something. > > I'm trying to build a query that among other things, returns the > minimum and maximum values contained in either of two columns. The > problem is that min() and max() only take one column name as an > argument. Is there some clever way that I can craft an expression to > find the min/max across both columns? I have yet to delve into any > pgsql so if it requires that, then please go easy on me. :) > > Example: > > id | columnA | columnB > 1 | 4 | 2 > 2 | 3 | 4 > 2 | 5 | 1 > > > I'd like to be able to discover that 1 is the smallest value in > either columnA or columnB and 5 is the largest. I don't actually care > what rows they are in, I just want the values. rk=# create table tab (id integer, a integer, b integer); CREATE TABLE rk=# insert into tab values (1, 4, 2); INSERT 0 1 rk=# insert into tab values (2,3,4); INSERT 0 1 rk=# insert into tab values (2,5,1); INSERT 0 1 rk=# select case when maxa > maxb then maxa else maxb end as max , case when mina < minb then mina else minb end as min from (select max(a) as maxa , max(b) as maxb , min(a) as mina , min(b) as minb from tab ) as tabalias; max | min -+- 5 | 1 (1 row) The reason for the subselect is to prevent multiple calculations of individual column aggregates. I believe it *may* be calculated multiple times otherwise this would work just as well: select case when max(a) > max(b) then max(a) else max(b) end as max from tab; -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] generate_series to return row that doesn't exist in
On Friday 24 March 2006 21:42, Scott Marlowe wrote: > More than likely you need a left join and a case statement. > > select , case when a.date is null then 0 else a.date end > from (select * from generate_series() -- magic to get dates goes here) > as p left join maintable as a on (p.date=a.date); > > There may be some small syntax error in there, as I've not tested it. > The relavent pages are: > > case: > http://www.postgresql.org/docs/8.1/static/functions-conditional.html > > joins: > http://www.postgresql.org/docs/8.1/static/queries-table-expressions.html Perfect!! Time to RTFM again... Thanks, -- MaXX ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Custom type
george young writes: > But not if you specify the object: > newschm3=# \d+ fffg > Table "public.fffg" > Column | Type | Modifiers | Description > +-+---+- > t | text| | > i | integer | | > This seems a bit like a bug to me... No, because that Description column is for comments on the individual columns. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Find min and max values across two columns?
Amos Hayes <[EMAIL PROTECTED]> writes: > I'm trying to build a query that among other things, returns the > minimum and maximum values contained in either of two columns. I think you might be looking for select greatest(max(columnA), max(columnB)) from tab; select least(min(columnA), min(columnB)) from tab; greatest/least are relatively new but you can roll your own in older PG releases. regards, tom lane ---(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
Re: [SQL] Find min and max values across two columns?
Rod Taylor <[EMAIL PROTECTED]> writes: > The reason for the subselect is to prevent multiple calculations of > individual column aggregates. I believe it *may* be calculated multiple > times otherwise this would work just as well: > select case when max(a) > max(b) then max(a) else max(b) end as max from > tab; Just for the record, we've gotten that right since 7.4. greatest() would be a notationally cleaner solution than CASE, but multiple occurrences of identical aggregates don't cost much of anything. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Find min and max values across two columns?
On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > The reason for the subselect is to prevent multiple calculations of > > individual column aggregates. I believe it *may* be calculated multiple > > times otherwise this would work just as well: > > > select case when max(a) > max(b) then max(a) else max(b) end as max from > > tab; > > Just for the record, we've gotten that right since 7.4. greatest() > would be a notationally cleaner solution than CASE, but multiple > occurrences of identical aggregates don't cost much of anything. Thanks. I could not remember one way or the other. -- ---(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
[SQL] Expressing a result set as an array (and vice versa)?
Good day, Is it possible to construct an array from an appropriate select expression that generates a result set of unknown cardinality? To focus on the simple case: Is it possible to construct a one- dimensional array from a select of a single column in a table with an unknown number of rows? Conversely, is it possible to construct a (single column) result set from a select expression on a one-dimensional array with an unknown number of elements? Thanks for any hints! Regards, Don Maier ---(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
[SQL] SQL Query Newbie Help
Given the two tables at the bottom of this email, I'm having trouble coming up with a SQL statement that returns all rows in the quality_control_reset table where there is only one row for the most recent quality_control_range. Help? Example: In table quality_control_reset: id | timestamp | qualitycontrolrange - 1| 02/23/2006 | 20 2| 02/23/2006 | 6 3| 02/28/2006 | 18 4| 03/01/2006 | 18 5| 03/23/2006 | 12 6| 03/23/2006 | 20 I want the results of the following from the query: id | timestamp | qualitycontrolrange - 2| 02/23/2006 | 6 4| 03/01/2006 | 18 5| 03/23/2006 | 12 6| 03/23/2006 | 20 CREATE TABLE quality_control_reset ( id int8 NOT NULL, date timestamp, qualitycontrolrange int8, CONSTRAINT quality_control_reset_pkey PRIMARY KEY (id), CONSTRAINT fk42a706efb62efa94 FOREIGN KEY (qualitycontrolrange) REFERENCES quality_control_range (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) CREATE TABLE quality_control_range ( id int8 NOT NULL, code varchar(255), CONSTRAINT quality_control_range_pkey PRIMARY KEY (id) ); ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] SQL Query Newbie Help
This works, but is there a better solution? select * from quality_control_reset T where date = ( select max(date) from quality_control_reset where qualitycontrolrange = T.qualitycontrolrange); Julie Robinson wrote: Given the two tables at the bottom of this email, I'm having trouble coming up with a SQL statement that returns all rows in the quality_control_reset table where there is only one row for the most recent quality_control_range. Help? Example: In table quality_control_reset: id | date| qualitycontrolrange - 1| 02/23/2006 | 20 2| 02/23/2006 | 6 3| 02/28/2006 | 18 4| 03/01/2006 | 18 5| 03/23/2006 | 12 6| 03/23/2006 | 20 I want the results of the following from the query: id | date| qualitycontrolrange - 2| 02/23/2006 | 6 4| 03/01/2006 | 18 5| 03/23/2006 | 12 6| 03/23/2006 | 20 CREATE TABLE quality_control_reset ( id int8 NOT NULL, date timestamp, qualitycontrolrange int8, CONSTRAINT quality_control_reset_pkey PRIMARY KEY (id), CONSTRAINT fk42a706efb62efa94 FOREIGN KEY (qualitycontrolrange) REFERENCES quality_control_range (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) CREATE TABLE quality_control_range ( id int8 NOT NULL, code varchar(255), CONSTRAINT quality_control_range_pkey PRIMARY KEY (id) ); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Find min and max values across two columns?
Your tips were great and I have hunted down the relevant pages in the docs. Thanks guys! -- Amos On 24-Mar-06, at 4:20 PM, Rod Taylor wrote: On Fri, 2006-03-24 at 16:20 -0500, Tom Lane wrote: Rod Taylor <[EMAIL PROTECTED]> writes: The reason for the subselect is to prevent multiple calculations of individual column aggregates. I believe it *may* be calculated multiple times otherwise this would work just as well: select case when max(a) > max(b) then max(a) else max(b) end as max from tab; Just for the record, we've gotten that right since 7.4. greatest() would be a notationally cleaner solution than CASE, but multiple occurrences of identical aggregates don't cost much of anything. Thanks. I could not remember one way or the other. -- ---(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 ---(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
[SQL] Index on nullable column
Hi, Is an index on a nullable column useful for retrieving rows having that column null? SELECT PlayerID FROM PlayerLoginSession WHERE EndTime IS NULL; Regards, -- Daniel CAUNE Ubisoft Online Technology (514) 4090 2040 ext. 5418 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL Query Newbie Help
On Fri, 24 Mar 2006, Julie Robinson wrote: > This works, but is there a better solution? > > select * > from quality_control_reset T > where date = ( > select max(date) > from quality_control_reset > where qualitycontrolrange = T.qualitycontrolrange); If you can use PostgreSQL extensions (and don't care that you might not get two rows if two ids had the same date equaling the max date for a given range), maybe something like: select distinct on (qualitycontrolrange) id, date, qualitycontrolrange from quality_control_reset order by qualitycontrolrange,date desc; Otherwise, you might see how the above compares in plan to something like (not really tested): select T.* from quality_control_reset T inner join (select qualitycontrolrange, max(date) as date from quality_control_reset group by qualitycontrolrange) T2 on (T.qualitycontrolrange = T2.qualitycontrolrange and T.date=T2.date); > Julie Robinson wrote: > > Given the two tables at the bottom of this email, I'm having trouble > > coming up with a SQL statement that returns all rows in the > > quality_control_reset table where there is only one row for the most > > recent quality_control_range. Help? > > > > Example: > > > > In table quality_control_reset: > > > > id | date| qualitycontrolrange > > - > > 1| 02/23/2006 | 20 > > 2| 02/23/2006 | 6 > > 3| 02/28/2006 | 18 > > 4| 03/01/2006 | 18 > > 5| 03/23/2006 | 12 > > 6| 03/23/2006 | 20 > > > > I want the results of the following from the query: > > > > id | date| qualitycontrolrange > > - > > 2| 02/23/2006 | 6 > > 4| 03/01/2006 | 18 > > 5| 03/23/2006 | 12 > > 6| 03/23/2006 | 20 > > > > > > CREATE TABLE quality_control_reset > > ( > > id int8 NOT NULL, > > date timestamp, > > qualitycontrolrange int8, > > CONSTRAINT quality_control_reset_pkey PRIMARY KEY (id), > > CONSTRAINT fk42a706efb62efa94 FOREIGN KEY (qualitycontrolrange) > > REFERENCES quality_control_range (id) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE NO ACTION > > ) > > > > CREATE TABLE quality_control_range ( > > id int8 NOT NULL, > > code varchar(255), > > CONSTRAINT quality_control_range_pkey PRIMARY KEY (id) > > ); ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Expressing a result set as an array (and vice versa)?
CREATE OR REPLACE FUNCTION foreach( liste INTEGER[] ) RETURNS SETOF
INTEGER AS $$
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..icount(liste) LOOP
RETURN NEXT liste[i];
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);
SELECT array_accum( DISTINCT list_id ) FROM bookmarks;
array_accum
---
{1,2,3,4,5,7}
SELECT * FROM foreach( '{1,2,3,4,5,7}' );
foreach
-
1
2
3
4
5
7
On Thu, 23 Mar 2006 20:44:32 +0100, Don Maier <[EMAIL PROTECTED]>
wrote:
Good day,
Is it possible to construct an array from an appropriate select
expression that generates a result set of unknown cardinality?
To focus on the simple case: Is it possible to construct a one-
dimensional array from a select of a single column in a table with an
unknown number of rows?
Conversely, is it possible to construct a (single column) result set
from a select expression on a one-dimensional array with an unknown
number of elements?
Thanks for any hints!
Regards,
Don Maier
---(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
---(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
Re: [SQL] Index on nullable column
"Daniel Caune" <[EMAIL PROTECTED]> writes: > Is an index on a nullable column useful for retrieving rows having that > column null? Nope, because IS NULL isn't an indexable operator. You can make an end-run around that with a partial index, eg create index fooi on foo(f1) where f1 is null This can be used to satisfy queries using "where f1 is null", but it's not any good for any other purpose. If you often do "where f1 is null and something-about-f2", you might get better mileage with create index fooi on foo(f2) where f1 is null but it's still a very specialized index. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] regarding join
hi all, below I have created two tables in pgsql with field name as 'name' and 'id' as their datatype 'varchar(15)' and 'integer'. One of the table is:-> chemical=> select * from test1; name | id ---+ akhil | 1 b | 2 c | 3 d | 4 e | 5 f | 6 (6 rows) Another table is:->chemical=> select * from test3; name | id --+ ab | 1 cd | 2 ef | 3 gh | 4 (4 rows) i want the output as:-> name | id ---+ akhil | 1 -from test1 table ab | 1--from test2 table b | 2-from test1 table cd | 2--from test2 table c | 3-from test1 table ef | 3--from test2 table d | 4-from test1 table gh | 4--from test2 table e | 5-from test1 table f | 6-from test1 table i have tried all the joins but it makes different fields for different tables. is there any way out for this kind of output?? (plz reply asap)urgent. THANKS IN ADVANCE -- Thanks & Regards,AkhileshS/W Trainee (EDP), NUCHEM Pvt. Ltd., Faridabad(Haryana)GSM:-(+919891606064)"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [SQL] regarding join
AKHILESH GUPTA <[EMAIL PROTECTED]> schrieb: > hi all, > below I have created two tables in pgsql with field name as 'name' and 'id' as > their datatype 'varchar(15)' and 'integer'. > i want the output as:-> ... a UNION of this 2 tables: test=# select * from test1 union select * from test3 order by 2,1; name | id ---+ ab| 1 akhil | 1 b | 2 cd| 2 c | 3 ef| 3 d | 4 gh| 4 e | 5 f | 6 (10 rows) HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly."(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(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
