Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

2008-09-22 Thread Yi Zhao 
yes, 
 select distinct max(pop),query from test
 group by query
test=# select distinct max(pop),query from bar group by query;
 max | query 
-+---
   8 | bar
  16 | def
  20 | foo
  30 | abc

but, I want to get the records contains more than two columns(max,
query, dfk), so, if I use group by, max, distinct keywords, I should
use this sql and get the result as below:
test=# select distinct max(pop),query, dfk from bar group by query, dfk;
 max | query | dfk 
-+---+-
   8 | bar   | are
  10 | abc   | 2
  15 | foo   | fk
  16 | def   | kj
  20 | foo   | lk
  30 | abc   | 1


btw: *distinct on* is useful:)

thanks, 

On Fri, 2008-09-19 at 09:00 -0700, Lennin Caro wrote:
 
 
 --- On Fri, 9/19/08, Yi Zhao [EMAIL PROTECTED] wrote:
 
  From: Yi Zhao [EMAIL PROTECTED]
  Subject: [GENERAL] how to return the first record from the sorted
 records which may have duplicated value.
  To: pgsql-general pgsql-general@postgresql.org
  Date: Friday, September 19, 2008, 8:51 AM
  hi all:
  I have a table with columns(2) named query,
  pop, dfk.
  what I want is:
  when I do some select, if the column query in
  result records have
  duplicate value, I only want the record which have the
  maximum value of
  the pop.
  
  for example, the content of table:
  query pop dfk
  ---
  abc30   1 --max
  foo 20   lk --max
  def 16   kj --max
  foo 15   fk --discard
  abc 10   2 --discard
  bar  8are --max
  
  the result should be:
  query pop dfk
  ---
  abc30   1
  foo 20   lk
  def 16   kj
  bar  8are
  
  now, I do it like this(plpgsql)
  
  declare hq := ''::hstore;
  begin
  for rc in execute 'select * from test order by pop
  desc' loop
  if not defined(hq, rc.query) then
  hq := hq || (rc.query = '1')::hstore;
  return next rc;
  end if;
  end loop;
  ---
  language sql/plpgsql will be ok.
  
  ps: I try to use group by or max
  function, because of the
  multi-columns(more than 2), I  failed. 
  
  thanks,
  any answer is appreciated.
  
  regards,
  
 
 
 this query work for me
 
 
 select distinct max(pop),query from test
 group by query
 
 
 please reply your results
 
 thanks...
 
 
 
 
 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

2008-09-22 Thread Yi Zhao 
yes, thanks u very much, it's work:)

regards,
Yi
On Fri, 2008-09-19 at 11:06 +0200, Andreas Kretschmer wrote:
 Yi Zhao [EMAIL PROTECTED] schrieb:
 
  hi all:
  I have a table with columns(2) named query, pop, dfk.
  what I want is:
  when I do some select, if the column query in result records have
  duplicate value, I only want the record which have the maximum value of
  the pop.
  
  for example, the content of table:
  query pop dfk
  ---
  abc30   1 --max
  foo 20   lk --max
  def 16   kj --max
  foo 15   fk --discard
  abc 10   2 --discard
  bar  8are --max
  
  the result should be:
  query pop dfk
  ---
  abc30   1
  foo 20   lk
  def 16   kj
  bar  8are
 
 test=*# select * from d;
  query | pop | dfk
 ---+-+-
  abc   |  30 | 1
  foo   |  20 | lk
  def   |  16 | kj
  foo   |  15 | fk
  abc   |  10 | 2
  bar   |   8 | are
 (6 Zeilen)
 
 Zeit: 0,213 ms
 test=*# select distinct on (query) * from d order by query, pop desc;
  query | pop | dfk
 ---+-+-
  abc   |  30 | 1
  bar   |   8 | are
  def   |  16 | kj
  foo   |  20 | lk
 (4 Zeilen)
 
 Hint: distinct on isn't standard-sql, it's an PG-extension.
 
 
 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.   (unknown)
 Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°
 


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to return the first record from the sorted records which may have duplicated value.

2008-09-19 Thread Yi Zhao 
hi all:
I have a table with columns(2) named query, pop, dfk.
what I want is:
when I do some select, if the column query in result records have
duplicate value, I only want the record which have the maximum value of
the pop.

for example, the content of table:
query pop dfk
---
abc30   1 --max
foo 20   lk --max
def 16   kj --max
foo 15   fk --discard
abc 10   2 --discard
bar  8are --max

the result should be:
query pop dfk
---
abc30   1
foo 20   lk
def 16   kj
bar  8are

now, I do it like this(plpgsql)

declare hq := ''::hstore;
begin
for rc in execute 'select * from test order by pop desc' loop
if not defined(hq, rc.query) then
hq := hq || (rc.query = '1')::hstore;
return next rc;
end if;
end loop;
---
language sql/plpgsql will be ok.

ps: I try to use group by or max function, because of the
multi-columns(more than 2), I  failed. 

thanks,
any answer is appreciated.

regards,


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

2008-09-19 Thread Andreas Kretschmer 
Yi Zhao [EMAIL PROTECTED] schrieb:

 hi all:
 I have a table with columns(2) named query, pop, dfk.
 what I want is:
 when I do some select, if the column query in result records have
 duplicate value, I only want the record which have the maximum value of
 the pop.
 
 for example, the content of table:
 query pop dfk
 ---
 abc30   1 --max
 foo 20   lk --max
 def 16   kj --max
 foo 15   fk --discard
 abc 10   2 --discard
 bar  8are --max
 
 the result should be:
 query pop dfk
 ---
 abc30   1
 foo 20   lk
 def 16   kj
 bar  8are

test=*# select * from d;
 query | pop | dfk
---+-+-
 abc   |  30 | 1
 foo   |  20 | lk
 def   |  16 | kj
 foo   |  15 | fk
 abc   |  10 | 2
 bar   |   8 | are
(6 Zeilen)

Zeit: 0,213 ms
test=*# select distinct on (query) * from d order by query, pop desc;
 query | pop | dfk
---+-+-
 abc   |  30 | 1
 bar   |   8 | are
 def   |  16 | kj
 foo   |  20 | lk
(4 Zeilen)

Hint: distinct on isn't standard-sql, it's an PG-extension.


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.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

2008-09-19 Thread Yi Zhao 
now, I do it like this(plpgsql)
---
this methold have low efficiency, when the records is large, it will
become slow, so someone can tell me some high efficiency way???

thanks.
On Fri, 2008-09-19 at 16:51 +0800, Yi Zhao wrote:

 hi all:
 I have a table with columns(2) named query, pop, dfk.
 what I want is:
 when I do some select, if the column query in result records have
 duplicate value, I only want the record which have the maximum value of
 the pop.
 
 for example, the content of table:
 query pop dfk
 ---
 abc30   1 --max
 foo 20   lk --max
 def 16   kj --max
 foo 15   fk --discard
 abc 10   2 --discard
 bar  8are --max
 
 the result should be:
 query pop dfk
 ---
 abc30   1
 foo 20   lk
 def 16   kj
 bar  8are
 
 now, I do it like this(plpgsql)
 
 declare hq := ''::hstore;
 begin
 for rc in execute 'select * from test order by pop desc' loop
   if not defined(hq, rc.query) then
   hq := hq || (rc.query = '1')::hstore;
   return next rc;
   end if;
 end loop;
 ---
 language sql/plpgsql will be ok.
 
 ps: I try to use group by or max function, because of the
 multi-columns(more than 2), I  failed. 
 
 thanks,
 any answer is appreciated.
 
 regards,

Re: [GENERAL] how to return the first record from the sorted records which may have duplicated value.

2008-09-19 Thread Lennin Caro 



--- On Fri, 9/19/08, Yi Zhao [EMAIL PROTECTED] wrote:

 From: Yi Zhao [EMAIL PROTECTED]
 Subject: [GENERAL] how to return the first record from the sorted records 
 which may have duplicated value.
 To: pgsql-general pgsql-general@postgresql.org
 Date: Friday, September 19, 2008, 8:51 AM
 hi all:
 I have a table with columns(2) named query,
 pop, dfk.
 what I want is:
 when I do some select, if the column query in
 result records have
 duplicate value, I only want the record which have the
 maximum value of
 the pop.
 
 for example, the content of table:
 query pop dfk
 ---
 abc30   1 --max
 foo 20   lk --max
 def 16   kj --max
 foo 15   fk --discard
 abc 10   2 --discard
 bar  8are --max
 
 the result should be:
 query pop dfk
 ---
 abc30   1
 foo 20   lk
 def 16   kj
 bar  8are
 
 now, I do it like this(plpgsql)
 
 declare hq := ''::hstore;
 begin
 for rc in execute 'select * from test order by pop
 desc' loop
   if not defined(hq, rc.query) then
   hq := hq || (rc.query = '1')::hstore;
   return next rc;
   end if;
 end loop;
 ---
 language sql/plpgsql will be ok.
 
 ps: I try to use group by or max
 function, because of the
 multi-columns(more than 2), I  failed. 
 
 thanks,
 any answer is appreciated.
 
 regards,
 


this query work for me


select distinct max(pop),query from test
group by query


please reply your results

thanks...





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general