[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?"
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 | | | (9 rows) However, if I try to count the records using the count(*) function I get the following error. ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in an aggregate function Surely, if postgres can execute the query it should also be able to count the rows that would be returned in the query? Is this a bug in Postgres and is there a work around? I am using Postgres 7.3. Thanks again! On Wednesday 12 Feb 2003 11:45 pm, Dmitry Tkach wrote: > Nicholas Allen wrote: > >Ok I thought of that but what happens if there is no primary key in the > > table? > > Then tough luck - as you said yourself, since everything else can be > duplicated, the primary key is the only way to > tell for sure which exact row you are talking about. > > >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. > > I think you are better off redesigning your interface - even if you do > modify the view, and use those suggestions I mentioned earlier, > it will be slow like hell, unless your table only contains a few hundred > rows, in which case it doesn't matter if you send the whole table to the > client every time, > > Dima > > >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] > > > >g ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
Ooops! Appologies to all for being sooo stupid!
I thought the order by was applied before the WHERE and if ordering in DESC
order for example < would mean greater than and so on in the where clause
because I assumed < meant it would appear before in the returned order.
Of course the where just selects which values and the order by orders them!
Don't know how I even got confused in the first place. So there's no bug in
POstgres and I just wasted a day being dumb (guess I should get more than 2
hours sleep before tackling this kinda stuff!).
Thanks all,
On Thursday 13 Feb 2003 8:39 pm, Manfred Koizar wrote:
> On Thu, 13 Feb 2003 18:28:50 +0100, Nicholas Allen
>
> <[EMAIL PROTECTED]> wrote:
> >Because the WHERE clause is directly affected by the ORDER BY clause.
>
> No, it's not (at least in your query).
>
> > If you
> >leave out the order by clause then the row count will be completely
> > different and therefore wrong.
>
> I must be missing something. Please give an example.
>
> > The ORDER BY clause is just as important as the WHERE
> >clause when counting rows. It should be possible to get a count for the
> > rows for any query that can be done which can return row data as I
> > understand it.
>
> If you have a set of numbers, say {1, 9, 5, 3, 7}, and want to know
> how many elements of the set are <= 7 (SELECT COUNT(*) FROM s WHERE
> n<=7), you simply look a each element - no matter in what order - and
> increase your counter, if the element satifies your condition. I
> can't see how you get different numbers when you count {1, 3, 5, 7},
> {1, 5, 3, 7} or any other permutation.
>
> >I have tried to find a definition for SQL SELECT command but everywhere I
> > have looked so far makes no mention of this being invalid SQL syntax. Can
> > you let me know where you got this information?
>
> SQL92 says:
> ::=
> [ ]
> [...]
> 3) Let T be the table specified by the .
>
> 4) If ORDER BY is specified, then each in the
> shall identify a column of T.
>
> Servus
> Manfred
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Debugging postmaster to fix possible bug in Postgres? Followup to "How do you select
Because the WHERE clause is directly affected by the ORDER BY clause. If you leave out the order by clause then the row count will be completely different and therefore wrong. The ORDER BY clause is just as important as the WHERE clause when counting rows. It should be possible to get a count for the rows for any query that can be done which can return row data as I understand it. I have tried to find a definition for SQL SELECT command but everywhere I have looked so far makes no mention of this being invalid SQL syntax. Can you let me know where you got this information? Thanks, On Thursday 13 Feb 2003 4:17 pm, Tom Lane wrote: > Nicholas Allen <[EMAIL PROTECTED]> writes: > > I then commented out the line just to see if this would fix the problem. > > Then I rebuilt it started the server up and connected. I performed the > > count query as I described bfore and it worked perfectly! It did exactly > > what I wanted! Now obviously the code was in there for some reason but it > > seems that it is not necessary to check it in this case. There must be a > > bug here surely. MySQL also allows it so I don't think it is invalid SQL > > on my part. And the fact it works perfectly if I disable this check is > > very promising. > > There is no bug here, or wasn't until you broke it. The given query is > illegal according to the SQL standard (MySQL is a fairly unreliable > guide to standard behavior :-(). It seems quite useless anyway: > "SELECT count(*)" will return exactly one row, so what's the meaning of > putting an ORDER BY clause on it? > > > Now if I execute this (note only difference is change from * to > > count(*)): > > > > select count(*) 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; > > > > I get this: > > > > ERROR: Attribute vu_tbl_user_all_s.s_surname must be GROUPed or used in > > an aggregate function > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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]
