Am I misusing the ORDER BY with CASE, or, what? :)
I have a table, messages, half dozen of columns, exposing here just
three of them:
pulitzer2=# select id, "from", receiving_time from messages where
service_id = 20 order by case when 5=5 then 2 else 3 end desc limit 5;
id | from |
+-le 13/02/2006 16:35 +0100, Mario Splivalo a dit :
| Am I misusing the ORDER BY with CASE, or, what? :)
|
| I have a table, messages, half dozen of columns, exposing here just
| three of them:
|
| pulitzer2=# select id, "from", receiving_time from messages where
| service_id = 20 order by case
On Mon, 2006-02-13 at 16:39 +0100, Mathieu Arnold wrote:
> |
> |
> | I tought I'd get differently sorted data, since in the first query I
> | said 5=5, and in second I said 5=6.
>
> Well, no, in the first, the result of the CASE is 2, and in the second 3, it
> means that for every line, it'll
On Mon, Feb 13, 2006 at 04:35:30PM +0100, Mario Splivalo wrote:
> Am I misusing the ORDER BY with CASE, or, what? :)
>
> I have a table, messages, half dozen of columns, exposing here just
> three of them:
>
> pulitzer2=# select id, "from", receiving_time from messages where
> service_id = 20 ord
+-le 13/02/2006 16:47 +0100, Mario Splivalo a dit :
| On Mon, 2006-02-13 at 16:39 +0100, Mathieu Arnold wrote:
|> |
|> | I tought I'd get differently sorted data, since in the first query I
|> | said 5=5, and in second I said 5=6.
|>
|> Well, no, in the first, the result of the CASE is 2, and in
Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
>
>> Bryce Nesbitt wrote:
>>
>>> They occur in finite time. That's good, thanks. But jeeze, can't
>>> postgres figure this out for itself?
>>>
>> I'm sure you wouldn't appreciate it if PostgreSQL did a full table scan
- Original Message -
From: "Mario Splivalo" <[EMAIL PROTECTED]>
If you keep your definition in a script file, you can copy the script and
paste it into pgAdmin's Execute Arbitrary SQL Queries window, and then
execute the script from there.
It's still a pain. If I have two dozen vie
Any idea why this works:
SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2
WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children WHERE
child_id = g2.id)
AND g2.id IN(1,2,109,105, 112);
And not this:
SELECT g.id, g.p_id, distinct(g.groupname) FROM onp_g
Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> Any idea why this works:
> SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2
> WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children
> WHERE
> child_id = g2.id)
> AND g2.id IN(1,2,109,105, 112);
> And n
Andreas Joseph Krogh wrote:
> Any idea why this works:
>
> SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g,
> onp_group g2
> WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM
> onp_group_children WHERE
> child_id = g2.id)
> AND g2.id IN(1,2,109,105, 112);
>
> And not this:
>
On Monday 13 February 2006 20:22, Tom Lane wrote:
> Andreas Joseph Krogh <[EMAIL PROTECTED]> writes:
> > Any idea why this works:
> > SELECT distinct(g.groupname), g.id, g.p_id FROM onp_group g, onp_group g2
> > WHERE g.id IN(SELECT g2.id UNION SELECT group_id FROM onp_group_children
> > WHERE chil
On Mon, 2006-02-13 at 17:10 +0100, Mathieu Arnold wrote:
> | It works like this:
> |
> | ORDER BY (CASE WHEN 5=5 THEN "from"::varchar ELSE
> | receiving_time::varchar) DESC.
> |
> | Is there a way to have DESC/ASC inside of a CASE?
> |
> | Mario
>
> No, you don't understand, you should do s
select l.lid,l.fs,max(h.obstime) from location as l
inner join height as h on h.lid = l.lid
where l.fs > 0.0
group by l.lid,l.fs;
The above query works as expected in that is fetches the lid, fs and
time of the latest observation in the height table (for the
corresponding lid), but I also want
I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table:
VACUUM ANALYZE ncccr10;
SELECT count(*) FROM ncccr10;
count
611564
(1 row)
When I try to analyze the query plan with:
EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
las
On Mon, Feb 13, 2006 at 05:48:45PM -0800, Ken Hill wrote:
> When I try to analyze the query plan with:
>
> EXPLAIN ANALYZE
> UPDATE ncccr10
> SET key = facilityno||'-'||
> lastname||'-'||
> sex||'-'||
> ssno||'-'||
> birthdate||'-'||
> primarysit||'-'||
> dxdate
--- Mark Fenbers <[EMAIL PROTECTED]> wrote:
> select l.lid,l.fs,max(h.obstime) from location as l
> inner join height as h on h.lid = l.lid
> where l.fs > 0.0
> group by l.lid,l.fs;
>
> The above query works as expected in that is fetches the lid, fs and
> time of the latest observation in the h
[Please copy the mailing list on replies.]
On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> date_part('year',dxdate) > '2000';
> cou
17 matches
Mail list logo
