pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedASK about SQL
6+ messages / 5 participants
[nested] [flat]
* ASK about SQL
@ 2007-05-31 07:56 ali nas <ali_llg21@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: ali nas @ 2007-05-31 07:56 UTC (permalink / raw)
To: pgsql-sql
Dear all,
HELP me PLZ............................................................................................
I'm used this SQL command :
"" SELECT ean FROM icitem WHERE ean in (4902715720005,4909411012151,4909411010164,4902715688718,4902715731605,4909411008864,4909411010188,4909411010126,4909411010140,490941101 2052,4902715720104,4909411010089,4909411010201,0,0) ; """";
The FILE content : but the result is :
ean
---------------
4902715720005 4902715720005
4909411012151 4902715720104
4909411010164 4902715688718
4902715688718 4902715731605
4902715731605 4909411008864
4909411008864 4909411010089
4909411010188 4909411010126
4909411010126 4909411010140
4909411010140 4909411010164
4909411012052 4909411010188
4902715720104 4909411010201
4909411010089 4909411012052
4909411010201 4909411012151
that is different RESULT with the input !!! what must I do if i dont want POSTGRES not ORDER my SELECT data ????
THX's
________________________________________________________
Kunjungi halaman depan Yahoo! Indonesia yang baru!
http://id.yahoo.com/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ASK about SQL
@ 2007-05-31 08:02 Phillip Smith <phillip.smith@weatherbeeta.com.au>
parent: ali nas <ali_llg21@yahoo.com>
0 siblings, 0 replies; 6+ messages in thread
From: Phillip Smith @ 2007-05-31 08:02 UTC (permalink / raw)
To: 'ali nas' <ali_llg21@yahoo.com>; pgsql-sql
Do you mean you want it to order it in the same order as you've listed them
in the IN condition?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of ali nas
Sent: Thursday, 31 May 2007 17:57
To: pgsql-sql@postgresql.org
Subject: [SQL] ASK about SQL
Dear all,
HELP me
PLZ.........................................................................
...................
I'm used this SQL command :
"" SELECT ean FROM icitem WHERE ean in
(4902715720005,4909411012151,4909411010164,4902715688718,4902715731605,49094
11008864,4909411010188,4909411010126,4909411010140,490941101
2052,4902715720104,4909411010089,4909411010201,0,0) ; """";
The FILE content : but the result is :
ean
---------------
4902715720005 4902715720005
4909411012151 4902715720104
4909411010164 4902715688718
4902715688718 4902715731605
4902715731605 4909411008864
4909411008864 4909411010089
4909411010188 4909411010126
4909411010126 4909411010140
4909411010140 4909411010164
4909411012052 4909411010188
4902715720104 4909411010201
4909411010089 4909411012052
4909411010201 4909411012151
that is different RESULT with the input !!! what must I do if i dont want
POSTGRES not ORDER my SELECT data ????
THX's
________________________________________________________
Kunjungi halaman depan Yahoo! Indonesia yang baru!
http://id.yahoo.com/
---------------------------(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
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: ASK about SQL
@ 2007-05-31 09:17 Phillip Smith <phillip.smith@weatherbeeta.com.au>
0 siblings, 0 replies; 6+ messages in thread
From: Phillip Smith @ 2007-05-31 09:17 UTC (permalink / raw)
To: 'ali nas' <ali_llg21@yahoo.com>; +Cc: pgsql-sql
Yes, you can order it the same as how you've listed in the IN condition, but
you need to use the ORDER BY clause... Essentially you just need to pass the
same list to both IN and ORDER BY but with some slightly different
formatting...
SELECT ean
FROM icitem
WHERE ean IN (4902715720005, 4909411012151, 4909411010164, 4902715688718,
4902715731605, 4909411008864, 4909411010188, 4909411010126, 4909411010140,
4909411012052, 4902715720104, 4909411010089, 4909411010201, 0)
ORDER BY CASE WHEN ean = 4902715720005 THEN '1'
WHEN ean = 4909411012151 THEN '2'
WHEN ean = 4909411010164 THEN '3'
WHEN ean = 4902715688718 THEN '4'
WHEN ean = 4902715731605 THEN '5'
WHEN ean = 4909411008864 THEN '6'
WHEN ean = 4909411010188 THEN '7'
WHEN ean = 4909411010126 THEN '8'
WHEN ean = 4909411010140 THEN '9'
WHEN ean = 4909411012052 THEN '10'
WHEN ean = 4902715720104 THEN '11'
WHEN ean = 4909411010089 THEN '12'
WHEN ean = 4909411010201 THEN '13'
WHEN ean = 0 THEN '14'
;
-----Original Message-----
From: ali nas [mailto:ali_llg21@yahoo.com]
Sent: Thursday, 31 May 2007 18:57
To: Phillip Smith
Subject: Hal: [SQL] ASK about SQL
YES, I don't care with with ORDER BY clause. beacuse i just want what the
data INPUT as like that the data OUTPUT ,so INPUT listed = OUTPUT listed.
so there isn't way in POSTGRES to do like this ??
----- Pesan Asli ----
Dari: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Kepada: ali nas <ali_llg21@yahoo.com>
Terkirim: Kamis, 31 Mei, 2007 3:44:12
Topik: RE: [SQL] ASK about SQL
You will need to explicitly tell Postgres what order you want it in - unless
you tell PG the order you want with an ORDER BY clause, it assumes you don't
care.
I can't recall 100% the exact syntax for the ORDER BY in your case, but
someone else more with it than me at the moment will be able to... Hang
tight! :)
-----Original Message-----
From: ali nas [mailto:ali_llg21@yahoo.com]
Sent: Thursday, 31 May 2007 18:31
To: Phillip Smith
Subject: Hal: [SQL] ASK about SQL
THX's for answered my question.
YES, i want POSTGRES just do SELECT in the IN condition. add clue, POSTGRES
will show the SELECT data which POSTGRES find first. I dont wanna like that.
So what will I do ??
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by
Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
________________________________________________________
Kunjungi halaman depan Yahoo! Indonesia yang baru!
http://id.yahoo.com/
*******************Confidentiality and Privilege Notice*******************
The material contained in this message is privileged and confidential to
the addressee. If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.
Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
^ permalink raw reply [nested|flat] 6+ messages in thread
* Ask About SQL
@ 2009-08-19 10:17 Otniel Michael <otnieltera@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Otniel Michael @ 2009-08-19 10:17 UTC (permalink / raw)
To: pgsql-sql
Hi All.
Can help to create sql queries for this data :
tabel A
field1, field2, field3
x1, y1, 5
x1, y2, 1
x2, y1, 2
x2, y3, 4
x1, y3, 4
I want to get 2 record with the max value at field3 for each kombination of
field1 :
tabel B
field1, field2, field3
x1, y1, 5
x1, y3, 4
x2, y3, 4
x2, y1, 2
Anyone have an ideas?
Thanks anyway.
--
-------------------------------------------------------------------
"He who is quick to become angry will commit folly, and a crafty man is
hated"
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Ask About SQL
@ 2009-08-19 10:46 A. Kretschmer <andreas.kretschmer@schollglas.com>
parent: Otniel Michael <otnieltera@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: A. Kretschmer @ 2009-08-19 10:46 UTC (permalink / raw)
To: pgsql-sql
In response to Otniel Michael :
> Hi All.
>
> Can help to create sql queries for this data :
>
> tabel A
> field1, field2, field3
> x1, y1, 5
> x1, y2, 1
> x2, y1, 2
> x2, y3, 4
> x1, y3, 4
>
> I want to get 2 record with the max value at field3 for each kombination of
> field1 :
>
> tabel B
> field1, field2, field3
> x1, y1, 5
> x1, y3, 4
> x2, y3, 4
> x2, y1, 2
>
> Anyone have an ideas?
Works since 8.4:
test=*# select * from table_a ;
field1 | field2 | field3
--------+--------+--------
x1 | y1 | 5
x1 | y2 | 1
x2 | y1 | 2
x2 | y3 | 4
x1 | y3 | 4
(5 rows)
test=*# select field1, field2, field3 from (select field1, field2,
field3, row_number() over(partition by field1 order by field3 desc) from
table_a order by field1, field3) foo where row_number < 3 order by
field1, field2;
field1 | field2 | field3
--------+--------+--------
x1 | y1 | 5
x1 | y3 | 4
x2 | y1 | 2
x2 | y3 | 4
(4 rows)
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Ask About SQL
@ 2009-08-19 14:00 Joshua Tolley <eggyknap@gmail.com>
parent: Otniel Michael <otnieltera@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Joshua Tolley @ 2009-08-19 14:00 UTC (permalink / raw)
To: Otniel Michael <otnieltera@gmail.com>; +Cc: pgsql-sql
On Wed, Aug 19, 2009 at 05:17:07PM +0700, Otniel Michael wrote:
> Hi All.
>
> Can help to create sql queries for this data :
>
> tabel A
> field1, field2, field3
> x1, y1, 5
> x1, y2, 1
> x2, y1, 2
> x2, y3, 4
> x1, y3, 4
>
> I want to get 2 record with the max value at field3 for each kombination
> of field1 :
>
> tabel B
> field1, field2, field3
> x1, y1, 5
> x1, y3, 4
> x2, y3, 4
> x2, y1, 2
>
> Anyone have an ideas?
> Thanks anyway.
>
> --
> -------------------------------------------------------------------
> "He who is quick to become angry will commit folly, and a crafty man is
> hated"
Your example doesn't match your description (the combination of x1 and y2
isn't listed). However, from your description it looks like what you want is
DISTINCT ON
http://www.postgresql.org/docs/8.4/interactive/sql-select.html#SQL-DISTINCT
Something like this:
SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM A ORDER BY
field1, field2, field3;
Here's an example.
# select * from b order by f1, f2, f3;
f1 | f2 | f3
----+----+----
x1 | y1 | 5
x1 | y2 | 1
x1 | y2 | 3
x2 | y3 | 2
x2 | y3 | 4
(5 rows)
# select distinct on (f1, f2) f1, f2, f3 from b order by 1, 2,
3 desc;
f1 | f2 | f3
----+----+----
x1 | y1 | 5
x1 | y2 | 3
x2 | y3 | 4
(3 rows)
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2009-08-19 14:00 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-31 07:56 ASK about SQL ali nas <ali_llg21@yahoo.com>
2007-05-31 08:02 ` Phillip Smith <phillip.smith@weatherbeeta.com.au>
2007-05-31 09:17 Re: ASK about SQL Phillip Smith <phillip.smith@weatherbeeta.com.au>
2009-08-19 10:17 Ask About SQL Otniel Michael <otnieltera@gmail.com>
2009-08-19 10:46 ` Re: Ask About SQL A. Kretschmer <andreas.kretschmer@schollglas.com>
2009-08-19 14:00 ` Re: Ask About SQL Joshua Tolley <eggyknap@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox