Re: [SQL] Help on function creating
On Fri, Dec 16, 2005 at 14:03:14 -0200, Alexandre Gonçalves Jacarandá <[EMAIL PROTECTED]> wrote: > Guys, I can do what I need but I have other problem: How can I named > columns with another data ? For example: You might be better off doing this in your application rather than entirely with sql. ---(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] Need SQL Help Finding Current Status of members
On Fri, Dec 16, 2005 at 07:44:46PM -0500, Michael Avila wrote: > Just tried it and it returned nothing. > > > > Select * from memberstatus A where not exists > > >(select * from emberstatus B where B.member_id=A.member_id and > > > B.status_date >A.status_date) > > Why the WHERE NOT EXISTS? The query selects each row in memberstatus for which no other rows (WHERE NOT EXISTS) with the same member_id have a later status_date; in other words, the row(s) with the latest status_date for each member_id. For example, given member_id | status_code | status_date ---+-+- 1 | a | 2005-12-01 1 | b | 2005-12-02 1 | c | 2005-12-03 2 | x | 2005-12-11 2 | y | 2005-12-12 2 | z | 2005-12-13 the query should return member_id | status_code | status_date ---+-+- 1 | c | 2005-12-03 2 | z | 2005-12-13 Offhand I can't think of why the query would return nothing unless the table is empty, but maybe I'm overlooking something or making unwarranted assumptions about the data. Can you post a sample data set for which the query returns no rows? -- Michael Fuhr ---(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] Need SQL Help Finding Current Status of members
The table is not empty. I did some playing around with the SQL Statement but got no where. So I added a record status column that will be non-NULL when the status becomes history because a new status is added. Yeah, I cheated to make it easy!! LOL But it now works and that is what counts! Thanks for the help. Mike > -Original Message- > From: Michael Fuhr [mailto:[EMAIL PROTECTED] > Sent: Saturday, December 17, 2005 7:26 PM > To: Michael Avila > Cc: SQL PostgreSQL MailList > Subject: Re: [SQL] Need SQL Help Finding Current Status of members > > > On Fri, Dec 16, 2005 at 07:44:46PM -0500, Michael Avila wrote: > > Just tried it and it returned nothing. > > > > > > Select * from memberstatus A where not exists > > > >(select * from emberstatus B where B.member_id=A.member_id and > > > > B.status_date >A.status_date) > > > > Why the WHERE NOT EXISTS? > > The query selects each row in memberstatus for which no other rows > (WHERE NOT EXISTS) with the same member_id have a later status_date; > in other words, the row(s) with the latest status_date for each > member_id. For example, given > > member_id | status_code | status_date > ---+-+- > 1 | a | 2005-12-01 > 1 | b | 2005-12-02 > 1 | c | 2005-12-03 > 2 | x | 2005-12-11 > 2 | y | 2005-12-12 > 2 | z | 2005-12-13 > > the query should return > > member_id | status_code | status_date > ---+-+- > 1 | c | 2005-12-03 > 2 | z | 2005-12-13 > > Offhand I can't think of why the query would return nothing unless > the table is empty, but maybe I'm overlooking something or making > unwarranted assumptions about the data. Can you post a sample data > set for which the query returns no rows? > > -- > Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
