[SQL] How do you select from a table until a condition is met?
Hi, I am hoping that someone can help me with a problem I have. I would like to be able to perform a SELECT query to select values from a table upto a certain row. The select may be ordered on a number of items. Is it possible to do this and how should I do it? The reason I need this is because I have created a virtual table browser class that performs SQL queries to get the sections of the table the user is interested in. This way I don't load the whole table onto the client side. If the user changes the sort ordering I want to determine the row index that was previously selected and scroll to that location. Any help would be very much appreciated. Thanks in advance, Nicholas Allen ---(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] Possible bug in Postgres? Followup to "How do you select from a table until a condition is met?"
Ok I have decided that this is the best way to do it. Assuming I have a column (or a few columns) which can uniquely define a row I should be able to select up to a known row with a clever where clause. For example a table contains the following dummy data (the combination of s_alias and s_loginid will be unique for each row): select * FROM vu_tbl_user_all_s ORDER BY s_surname, s_loginid; s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled | s_4eyestype | s_usertype | b_hasvaluation | d_dob|s_email| s_phone | s_phone_mb -+-+-+---+++-++++---+-+ CISX| 3 A Surname | 2aname | | Loginid3 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| A Asurname | A Aname | | Loginid2 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish3 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish4 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish5 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish6 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aoname | | Lloginid || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Loginid|| No restrictions | CISX Administrator ||| [EMAIL PROTECTED] | | CISX| Asurname| Aname | | Loginid8 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Loginid9 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| A Surname | Aname | | Loginid4 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Cisx| Cursor | | Systemuser | f | No restrictions | CISX Administrator | f | 1966-01-23 | | | CISX| Dazor | Ronald | | Demolish | t | No restrictions | CISX Administrator || 1967-12-12 | | | CISX| Dazor | Ronald | | Demolish2 | f | No restrictions | CISX Administrator || 1967-12-12 | | | (14 rows) I should be able to select up to row 9 (Loginid8)using this query: select * FROM vu_tbl_user_all_s WHERE s_surname < 'Asurname' or (s_surname = 'Asurname' and s_alias <= 'CISX' and s_loginid <= 'Loginid8') ORDER BY s_surname, s_loginid; This works and I get the following (expected) output: s_alias | s_surname | s_name | s_midname | s_loginid | b_disabled | s_4eyestype | s_usertype | b_hasvaluation | d_dob|s_email| s_phone | s_phone_mb -+-+-+---+---++-++++---+-+ CISX| 3 A Surname | 2aname | | Loginid3 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| A Asurname | A Aname | | Loginid2 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish3 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish4 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aname | | Demolish5 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aso it is not the very latest version.name | | Demolish6 || No restrictions | CISX Administrator || 1966-12-12 | | | CISX| Asurname| Aoname | | Lloginid || No restrictions | CISX Administrator || 1966-12-12 |
Re: [SQL] null foreign key column
12/02/2003 2:24:49 PM, Dmitry Tkach <[EMAIL PROTECTED]> wrote: >You don't want it to be serial - just make it 'person_id in' Any reasons? > >I hope, it helps... > >Dima > >Arunachalam Jaisankar wrote: >> This is a multi-part message in MIME format. >> >> --=_NextPart_000_0005_01C2D1EE.61998D70 >> Content-Type: text/plain; >> charset="iso-8859-1" >> Content-Transfer-Encoding: quoted-printable >> >> Hi all, >> >> I would like to have a foreign key column in my table which allows null val= >> ue also. >> But the below create table sql command doesn't accept null value for person= >> _id. >> How to do in postgres? >> >> create table event >> ( >> event_id serialnot null, >> event_description char(255) , >> person_id serial, >> primary key (event_id), >> foreign key (person_id) >>references person (person_id) >> ); >> >> regards >> Jai >> --=_NextPart_000_0005_01C2D1EE.61998D70 >> Content-Type: text/html; >> charset="iso-8859-1" >> Content-Transfer-Encoding: quoted-printable >> >> >> >> >> >> >> >> >> Hi all, >> >> I would like to have a foreign key column = >> in my=20 >> table which allows null value also. >> But the below create table sql command doe= >> sn't=20 >> accept null value for person_id. >> How to do in postgres? >> = >> >> create table event( &nb= >> sp;=20 >> event_id &= >> nbsp; =20 >> serial &nb= >> sp; =20 >> not null, event_description&n= >> bsp;=20 >> char(255) = >> =20 >> , =20 >> person_id = >> =20 >> serial &nb= >> sp; =20 >> , primary key (event_id), forei= >> gn=20 >> key (person_id) references pe= >> rson=20 >> (person_id)); >> >> regards >> Jai >> >> --=_NextPart_000_0005_01C2D1EE.61998D70-- >> > > >---(end of broadcast)--- >TIP 2: you can get off all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] null foreign key column
Vernon Wu wrote: 12/02/2003 2:24:49 PM, Dmitry Tkach <[EMAIL PROTECTED]> wrote: You don't want it to be serial - just make it 'person_id in' Any reasons? Yeah... Two of them: - It does not make sense for a serial column to reference other tables - the only purpose of serial is to generate unique keys, in your case you do not want them generated, but rather copied from the entries in the referenced table. - The other reason is that 'serial' implies 'not null' - that is why your insert statement fails. Dima I hope, it helps... Dima Arunachalam Jaisankar wrote: This is a multi-part message in MIME format. --=_NextPart_000_0005_01C2D1EE.61998D70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I would like to have a foreign key column in my table which allows null val= ue also. But the below create table sql command doesn't accept null value for person= _id. How to do in postgres? create table event ( event_id serialnot null, event_description char(255) , person_id serial, primary key (event_id), foreign key (person_id) references person (person_id) ); regards Jai --=_NextPart_000_0005_01C2D1EE.61998D70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I would like to have a foreign key column = in my=20 table which allows null value also. But the below create table sql command doe= sn't=20 accept null value for person_id. How to do in postgres? = create table event( &nb= sp;=20 event_id &= nbsp; =20 serial &nb= sp; =20 not null, event_description&n= bsp;=20 char(255) = =20 , =20 person_id = =20 serial &nb= sp; =20 , primary key (event_id), forei= gn=20 key (person_id) references pe= rson=20 (person_id)); regards Jai --=_NextPart_000_0005_01C2D1EE.61998D70-- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] null foreign key column
You don't want it to be serial - just make it 'person_id in' I hope, it helps... Dima Arunachalam Jaisankar wrote: This is a multi-part message in MIME format. --=_NextPart_000_0005_01C2D1EE.61998D70 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I would like to have a foreign key column in my table which allows null val= ue also. But the below create table sql command doesn't accept null value for person= _id. How to do in postgres? create table event ( event_id serialnot null, event_description char(255) , person_id serial, primary key (event_id), foreign key (person_id) references person (person_id) ); regards Jai --=_NextPart_000_0005_01C2D1EE.61998D70 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I would like to have a foreign key column = in my=20 table which allows null value also. But the below create table sql command doe= sn't=20 accept null value for person_id. How to do in postgres? = create table event( &nb= sp;=20 event_id &= nbsp; =20 serial &nb= sp; =20 not null, event_description&n= bsp;=20 char(255) = =20 , =20 person_id = =20 serial &nb= sp; =20 , primary key (event_id), forei= gn=20 key (person_id) references pe= rson=20 (person_id)); regards Jai --=_NextPart_000_0005_01C2D1EE.61998D70-- ---(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] How do you select from a table until a condition is met?
Ok I thought of that but what happens if there is no primary key in the table? I can probably add primary keys to the table but I didn't design the tables and so I have little (but luckily some) say over what columns appear in them. What has actually happened is that we have a view on a table and the view doesn't return the primary key. I'll try and ask the database administrator to add the primary keys. Thanks for the help though I guess it is the only way to do it. I was just hoping there would be a way to do it without a promary key to prevent changes to our database views. On Wednesday 12 Feb 2003 9:37 pm, Bruno Wolff III wrote: > On Wed, Feb 12, 2003 at 20:55:21 +0100, > > Nicholas Allen <[EMAIL PROTECTED]> wrote: > > I thought of this but the problem is that there may be multiple rows with > > the same value for the column I am sorting on. Eg if sorting on a surname > > then there may be 100s of people with the same surname so generating a > > where clause that selects up to the exact person previously selected is > > very difficult. > > Then you should sort on surname AND whatever you are using as the primary > key. > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
