[SQL] Merge Record in Database(SQL Statement)
Hai.. How to merge record in database(sql statement) if record same attribute, examples:- Attribute Major Birth_Place GPA Record 1science India excellent Record 2science India excellent Please help me.. Powered by Fastmail from http://www.i-fastmail.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Merge Record in Database(SQL Statement)
Not quite sure what the question is Assuming, you would like to normalize the data, I suggest to create 4 tables: create table gpa (id integer primary key,gpa varchar(32) not null unique); create table major (id integer primary key,major varchar(32) not null unique); create table birthplace (id integer primary key,birthplace varchar(32) not null unique); create table xxx ( id integer primary key, attributename varchar(64) not null, -- this might be unique too id_major integer references major(id), id_birthplace integer references birthplace(id), id_gpa integer references gpa(id) ); insert into gpa (id,gpa) values (1,'excellent'); insert into major (id,major) values (1,'science'); insert into birthplace (id,birthplace) 1,'India'); insert into xxx (id,attributename,id_major,id_birthplace,id_gpa) values (1,'Record 1',1,1,1); insert into xxx (id,attributename,id_major,id_birthplace,id_gpa) values (1,'Record 2',1,1,1); Now selecting the records would be something like: select x.attributename,m.major,b.birthplace,g.gpa from attributename a,major m,birthplace b,gpa g where x.id_major=m.id and x.id_birthplace=b.id and x.id_gpa=g.id Hope that helps Detlef -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Auftrag von Shahbuddin Md Isa Gesendet: Montag, 7. Juli 2003 03:24 An: [EMAIL PROTECTED] Betreff: [SQL] Merge Record in Database(SQL Statement) Hai.. How to merge record in database(sql statement) if record same attribute, examples:- Attribute Major Birth_Place GPA Record 1science India excellent Record 2science India excellent Please help me.. Powered by Fastmail from http://www.i-fastmail.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] create view error
Hi folks,
I know I'm missing something blindingly obvious, can someone point it out to
me please.
create table locos (-- Locos table - contains details of locos
lid int4 default nextval('loco_lid_seq'::text) unique not null,
lclass int4 references lclass(lcid), -- Loco Class
lbuilt date, -- Date off-shed
lcmeint4 references cme(cmid), -- Chief Mechanical Engineer
lname character varying(30), -- Name of Loco
lcomments text-- free text comments
);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
table 'locos'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create table lnumbers ( -- alternate loco numbers
lnidint4 not null references locos(lid),
lnumber character varying(10),
lncurrent bool,
primary key (lnid, lnumber)
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
for table 'lnumbers'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
create view loco_dets as
select * from locos l
left outer join
(select * from lclass) lc on lc.lcid = l.lclass
left outer join
(select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
= true
left outer join
(select * from company) c on c.coid = lc.lcompany;
ERROR: No such attribute or function ln.lnid
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] create view error
On Mon, 7 Jul 2003, Gary Stainburn wrote:
> Hi folks,
>
> I know I'm missing something blindingly obvious, can someone point it out to
> me please.
>
> create table locos ( -- Locos table - contains details of locos
> lid int4 default nextval('loco_lid_seq'::text) unique not null,
> lclassint4 references lclass(lcid), -- Loco Class
> lbuiltdate, -- Date off-shed
> lcme int4 references cme(cmid), -- Chief Mechanical Engineer
> lname character varying(30), -- Name of Loco
> lcomments text-- free text comments
> );
> NOTICE: CREATE TABLE / UNIQUE will create implicit index 'locos_lid_key' for
> table 'locos'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create table lnumbers ( -- alternate loco numbers
> lnid int4 not null references locos(lid),
> lnumber character varying(10),
> lncurrent bool,
> primary key (lnid, lnumber)
> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'lnumbers_pkey'
> for table 'lnumbers'
> NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> CREATE
> create view loco_dets as
> select * from locos l
> left outer join
> (select * from lclass) lc on lc.lcid = l.lclass
> left outer join
> (select lnumber from lnumbers) ln on ln.lnid = l.lid and ln.lncurrent
^^^
select also lnid
> = true
> left outer join
> (select * from company) c on c.coid = lc.lcompany;
> ERROR: No such attribute or function ln.lnid
>
--
==
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel:+30-210-8981112
fax:+30-210-8981877
email: achill at matrix dot gatewaynet dot com
mantzios at softlab dot ece dot ntua dot gr
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] create view error
On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > left outer join > (select lnumber from lnumbers) ln on ln.lnid = l.lid and ^^^ ^^^ > ERROR: No such attribute or function ln.lnid Is is this? -- Richard Huxton ---(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
Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote: > On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > > left outer join > > (select lnumber from lnumbers) ln on ln.lnid = l.lid and > > ^^^ ^^^ > > > ERROR: No such attribute or function ln.lnid > > Is is this? Yup, thanks to both of you for this answer. Is there any way to do this so that lnid is not visible in the resulting view? Also, using examples from this list, I've created a concat function and aggregate so that I can convert a number of rows to a comma delimited string. I can then use this in a select as shown below, but what I can't work out is how to put this into my join. I want to include the second of the two selects shown below (the one with 'lncurrent = true' where clause) into my view (shown at bottom). I can't work out where to put the where and group by clauses. nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers group by lnid; lnid | lnalternate --+-- 1 | 29 2 | 2392,65894 3 | 4277 4 | 80135 5 | 30926,926 6 | 45212 7 | 44767 8 | 60532 9 | 75014 10 | 75029 11 | 60007 12 | 25 278,D7628 13 | 08850,4518 14 | 62005,62012 15 | 24 061,D5061 16 | 45337 17 | 6619 18 | 64360,901 19 | 5 20 | 825 21 | 45157 22 | 76079 23 | 4771,60800 24 | 55019,D9019 25 | D9009 26 | 08556,D3723 (26 rows) nymr=# select lnid, concat(lnumber) as lnalternate from lnumbers where lncurrent = false group by lnid; lnid | lnalternate --+- 2 | 2392 5 | 926 12 | 25 278 13 | 08850 14 | 62012 18 | 64360 23 | 4771 24 | D9019 26 | D3723 (9 rows) nymr=# create view loco_dets as select * from locos l left outer join lclass lc on lc.lcid = l.lclass left outer join lnumbers n on n.lnid = l.lid and n.lncurrent = true left outer join (select lnid, concat(lnumber) as lnalternate, lncurrent from lnumbers ) na on na.lnid = l.lid and na.lncurrent = false left outer join company c on c.coid = lc.lcompany; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 1:36 pm, you wrote: > On Monday 07 Jul 2003 1:07 pm, Richard Huxton wrote: > > On Monday 07 Jul 2003 12:40 pm, Gary Stainburn wrote: > > > left outer join > > > (select lnumber from lnumbers) ln on ln.lnid = l.lid and > > > > ^^^ ^^^ > > > > > ERROR: No such attribute or function ln.lnid > > > > Is is this? > > Yup, thanks to both of you for this answer. > > Is there any way to do this so that lnid is not visible in the resulting > view? > I've managed to get the view I wanted by using sub-selects - as shown below, but I now have the 'lid' field showing in the resulting view three times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the result? create view loco_dets as select * from locos l left outer join lclass lc on lc.lcid = l.lclass left outer join lnumbers n on n.lnid = l.lid and n.lncurrent = true left outer join (select lnid as lnaid, concat(lnumber) as lnalternate from (select lnid, lnumber from lnumbers where lncurrent = false order by lnid, lnumber) alt group by lnaid) na on na.lnaid = l.lid left outer join company c on c.coid = lc.lcompany; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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] sort for ranking
Hello *,
I have a little problem that confuses me. We are gathering values from a table
as a sum to insert them into another table. I also need to get a ranking at
insert (i.e. Highest points will get first place and so on). I tried ton
invole a sequence to qualify the ranking by select at insert.
So I tried the following (smaller example)
select setval('tipp_eval_seq',1);
select sum_user,nextval('tipp_eval_seq')-1 as ranking from tbl_sums order by
ranking desc, user_sum asc;
user_sum | ranking
--+-
46 | 30
45 | 26
44 | 28
43 | 25
42 | 1
41 | 2
39 | 3
38 | 27
36 | 19
35 | 18
34 | 20
31 | 24
30 | 17
29 | 15
28 | 16
27 | 12
26 | 11
25 | 23
24 | 21
23 | 10
19 | 13
16 | 9
12 | 7
11 | 8
10 | 29
8 | 6
7 | 5
6 | 14
2 | 4
1 | 22
(30 rows)
As you can see, the sums are sorted correctly but the ranking is a mess. I
recongnized that the select seems to follow primarily the internal table
order. Is there any way to solve this nicely. Hints and solutions are
appreciated.
Thanks in advance
-Andreas
--
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email [EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote: > > I've managed to get the view I wanted by using sub-selects - as shown > below, but I now have the 'lid' field showing in the resulting view three > times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the > result? > > create view loco_dets as > select * from locos l > left outer join [snip] Don't do "select *" do "select field_a,field_b..." - the * doesn't just refer to the locos table. -- Richard Huxton ---(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
Re: Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote: > On Monday 07 Jul 2003 2:12 pm, Gary Stainburn wrote: > > I've managed to get the view I wanted by using sub-selects - as shown > > below, but I now have the 'lid' field showing in the resulting view three > > times (as lid, lnid and lnaid). How can I remove lnid and lnaid from the > > result? > > > > create view loco_dets as > > select * from locos l > > left outer join > > [snip] > > Don't do "select *" do "select field_a,field_b..." - the * doesn't just > refer to the locos table. Sorry if I didn't make myself plain enough, but I had create view loco_dets as select * from locos l -- includes lid which I want left outer join (select lnid, lnumber...) ln on ln.lnid = l.lid ... The problem is that I have to have lnid in the sub-select to allow the 'on' clause to work, but I don't want lnid to appear in the resulting view. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Database Upgrade scripts (AKA Conditional SQL execution)
Richard Rowell wrote: Can I accomplish this with postgresql without involving an external process (like say perl)? I guess I could put the upgrade stuff into PL/SQL functions and just drop the functions when I'm done, but I was hoping for something a little "cleaner". There is no way (currently at least) to do this without some kind of function. I've done it in the past with PL/pgSQL. We've had brief discussions in the past about how this could be supported, but nothing conclusive, and no one has cared enough to scratch this particular itch. Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: Concat and view - Re: [SQL] create view error
On Monday 07 Jul 2003 4:10 pm, Gary Stainburn wrote: > On Monday 07 Jul 2003 3:34 pm, Richard Huxton wrote: > > Don't do "select *" do "select field_a,field_b..." - the * doesn't just > > refer to the locos table. > > Sorry if I didn't make myself plain enough, but I had > > create view loco_dets as > select * from locos l -- includes lid which I want >left outer join > (select lnid, lnumber...) ln on ln.lnid = l.lid > ... > > The problem is that I have to have lnid in the sub-select to allow the 'on' > clause to work, but I don't want lnid to appear in the resulting view. Yep, so don't do "SELECT *", list the fields you want instead. The * in that second line applies to the rest of the query, not just the "locos" table. There is something to be said for a format such as "SELECT * EXCEPT lnid" but I don't think it's mentioned in any sql spec. -- Richard Huxton ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] max length of sql select statement ?
Hi All! I was searching the archive and was wondering why nobody asked this strange(!) question (or I've not found it?): "What is the max allowed length of a sql statement or query?" I want to combine hundrets or thousands 'OR' within a select statement. Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR col='c' OR .. ) This would be a very "dirty" or some would say, a "horrible" solution; but how are you searching through your table with sql, when your SELECT is against a collection of different elements (e.g. Array of Strings, which should fit on one column and return all records which fit) Hope for help cheers Markus - sql beginner ;-) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] max length of sql select statement ?
Depending on ur original problem EXISTS or IN may be usable EXISTS is efficient and IN can be used efficiently in 7.4 version of postgresql regds mallah. > Hi All! > > I was searching the archive and was wondering why nobody asked this > strange(!) question (or I've not found it?): > > "What is the max allowed length of a sql statement or query?" > I want to combine hundrets or thousands 'OR' within a select statement. > Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR > col='c' OR .. ) > > This would be a very "dirty" or some would say, a "horrible" solution; > > but how are you searching through your table with sql, when your SELECT > is against a collection of different elements (e.g. Array of Strings, > which should fit on one column and return all records which fit) > > Hope for help > cheers Markus - sql beginner ;-) > > > > > ---(end of > broadcast)--- TIP 1: subscribe and unsubscribe > commands go to [EMAIL PROTECTED] - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Need help with complex query
Suppose I have a relation called sales with the following schema: sales-Schema = (cust, prod, mn, dy, yr, quant, st) An instance of the relation would look something like this: custprodmn dy yr quant st == == == = == Knuth Milk02 29 200012 CA Jones Fruits 03 31 200145 WY Knuth Jam 12 21 200241 MN Kruskal Jelly 11 30 199912 NJ Hamming Milk03 15 199847 GA Knuth Milk02 29 200012 CA Kruskal Jelly 11 30 19995 NJ Knuth Milk06 23 200212 CA Knuth Bread 02 21 194913 CA Note: The relation can have duplicate rows. Now, I want this query: For each customer-product combination, find the minimum quantity sold along with its respective date. If there are more than one minimum sales quantity for a customer product combination, print only one of them. So the query should return the following: custprodmn dy yr quant == == == = Jones Fruits 03 31 200145 Knuth Jam 12 21 200241 Hamming Milk03 15 199847 Kruskal Jelly 11 30 19995 Knuth Milk06 23 200212 Knuth Bread 02 21 194913 I wrote the following query: select cust, prod, mn, dy, yr, quant from (select cust, prod, min(quant) from sales group by cust, prod) as x(c, p, q), sales where cust = x.c and prod = x.p and quant = x.q; And I got the following relation: custprodmn dy yr quant == == == = Knuth Milk02 29 200012 Jones Fruits 03 31 200145 Knuth Jam 12 21 200241 Hamming Milk03 15 199847 Knuth Milk02 29 200012 Kruskal Jelly 11 30 19995 Knuth Milk06 23 200212 Knuth Bread 02 21 194913 which is not what I want because the Knuth-Bread combination is repeated; I only want one of them. I have tried many other variations of the query, but the best I've done is something like this (by selection distinct quantities out of the above table): custprodmn dy yr quant st == == == = == Jones Fruits 03 31 200145 WY Knuth Jam 12 21 200241 MN Hamming Milk03 15 199847 GA Knuth Milk02 29 200012 CA Kruskal Jelly 11 30 19995 NJ Knuth Milk06 23 200212 CA Knuth Bread 02 21 194913 CA Can anyone help me out? Thanks in advance. ---(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] max length of sql select statement ?
On Mon, 2003-07-07 at 17:57, [EMAIL PROTECTED] wrote:
> Depending on ur original problem EXISTS or IN may be usable
> EXISTS is efficient and IN can be used efficiently in 7.4 version
> of postgresql
Could be a solution?!
The question is - how long could the IN be?
I mean, if I write something like:
SELECT * FROM table WHERE columnX IN ('a', 'b', 'c', 'd', 'e', ... );
How long can the collection (list) within IN be? Also thousands of
elements?
And what means efficient? Goes the DB only once through the table?
Cheers, Markus
---(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] max length of sql select statement ?
> On Mon, 2003-07-07 at 17:57, [EMAIL PROTECTED] wrote:
>> Depending on ur original problem EXISTS or IN may be usable
>> EXISTS is efficient and IN can be used efficiently in 7.4 version of
>> postgresql
>
> Could be a solution?!
> The question is - how long could the IN be?
>
> I mean, if I write something like:
> SELECT * FROM table WHERE columnX IN ('a', 'b', 'c', 'd', 'e', ... );
> How long can the collection (list) within IN be? Also thousands of
> elements?
Well i DO NOT know the exact limit.
May be someone else can answer it accurately.
But you could produce the list within IN using a subselect
that again depends on the exact problem.
regds
Mallah.
>
> And what means efficient? Goes the DB only once through the table?
>
> Cheers, Markus
>
>
>
>
> ---(end of
> broadcast)--- TIP 2: you can get off all lists
> at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] max length of sql select statement ?
> Could be a solution?! > The question is - how long could the IN be? I'm not sure about IN specifically, but I know you can do: SELECT * FROM table WHERE col = '<1GB long file>'; It tends not to be friendly for Ram though :) signature.asc Description: This is a digitally signed message part
Re: [SQL] max length of sql select statement ?
On Mon, 2003-07-07 at 18:13, [EMAIL PROTECTED] wrote: > Well i DO NOT know the exact limit. > May be someone else can answer it accurately. > > But you could produce the list within IN using a subselect > that again depends on the exact problem. > Maybe anybody knows how many? Anyway: My exact problem is "in words" quite easy: col1 | col2 123 | 958 143 | 394 124 | 345 324 | 345 346 | 541 743 | 144 346 | 986 Imagine, this table is really big (millions records). Now, I want to retrieve for all records in col A OR col B where either 123, 124, 144, 541 (and a view thousands more ...) fits. As far as I understud you: SELECT * FROM table WHERE col1 IN (123,124,144,541,...) OR col2 IN (123,124,144,541,...); Cheers, Markus ---(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
Re: [SQL] max length of sql select statement ?
On 7 Jul 2003, markus brosch wrote: > I was searching the archive and was wondering why nobody asked this > strange(!) question (or I've not found it?): > > "What is the max allowed length of a sql statement or query?" AFAIR in recent versions it's effectively limited only by resources (how much bandwidth/memory do you want to use). > I want to combine hundrets or thousands 'OR' within a select statement. > Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR > col='c' OR .. ) It should be possible. > This would be a very "dirty" or some would say, a "horrible" solution; > > but how are you searching through your table with sql, when your SELECT > is against a collection of different elements (e.g. Array of Strings, > which should fit on one column and return all records which fit) Are the 'a', 'b', etc... fixed or generated on the fly? I'm not sure that the plan for a thousand OR clauses (or equivalently a thousand entries in a static IN) is going to necessarily be terribly good. You might have better luck setting it up to do a join with a table (possibly a temporary table if they're generated on the fly). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] max length of sql select statement ?
> > but how are you searching through your table with sql, when your SELECT > > is against a collection of different elements (e.g. Array of Strings, > > which should fit on one column and return all records which fit) > > Are the 'a', 'b', etc... fixed or generated on the fly? I'm not sure > that the plan for a thousand OR clauses (or equivalently a thousand > entries in a static IN) is going to necessarily be terribly good. You > might have better luck setting it up to do a join with a table (possibly a > temporary table if they're generated on the fly). Generated "on the fly" by JDBC within Java ;-) I already tried the join, but it takes ages *g* ---(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] sort for ranking
Andreas,
try
select sum_user,nextval('tipp_eval_seq')-1 as ranking from (
select user_sum from tbl_sums order by user_sum desc) as ss;
JLL
Andreas Schmitz wrote:
>
> Hello *,
>
> I have a little problem that confuses me. We are gathering values from a table
> as a sum to insert them into another table. I also need to get a ranking at
> insert (i.e. Highest points will get first place and so on). I tried ton
> invole a sequence to qualify the ranking by select at insert.
>
> So I tried the following (smaller example)
>
> select setval('tipp_eval_seq',1);
> select sum_user,nextval('tipp_eval_seq')-1 as ranking from tbl_sums order by
> ranking desc, user_sum asc;
>
> user_sum | ranking
> --+-
>46 | 30
>45 | 26
>44 | 28
>43 | 25
>42 | 1
>41 | 2
>39 | 3
>38 | 27
>36 | 19
>35 | 18
>34 | 20
>31 | 24
>30 | 17
>29 | 15
>28 | 16
>27 | 12
>26 | 11
>25 | 23
>24 | 21
>23 | 10
>19 | 13
>16 | 9
>12 | 7
>11 | 8
>10 | 29
> 8 | 6
> 7 | 5
> 6 | 14
> 2 | 4
> 1 | 22
> (30 rows)
>
> As you can see, the sums are sorted correctly but the ranking is a mess. I
> recongnized that the select seems to follow primarily the internal table
> order. Is there any way to solve this nicely. Hints and solutions are
> appreciated.
>
> Thanks in advance
>
> -Andreas
>
> --
> Andreas Schmitz - Phone +49 201 8501 318
> Cityweb-Technik-Service-Gesellschaft mbH
> Friedrichstr. 12 - Fax +49 201 8501 104
> 45128 Essen - email [EMAIL PROTECTED]
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] sort for ranking
I'm gonna guess you stored your ranking as a "text" field, but now you'd
like to treat it like an int / numeric.
While it would be better to go ahead and convert it, you can always cast
it:
select * from table order by textfield::int;
On Mon, 7 Jul 2003, Andreas Schmitz wrote:
>
> Hello *,
>
> I have a little problem that confuses me. We are gathering values from a table
> as a sum to insert them into another table. I also need to get a ranking at
> insert (i.e. Highest points will get first place and so on). I tried ton
> invole a sequence to qualify the ranking by select at insert.
>
> So I tried the following (smaller example)
>
> select setval('tipp_eval_seq',1);
> select sum_user,nextval('tipp_eval_seq')-1 as ranking from tbl_sums order by
> ranking desc, user_sum asc;
>
> user_sum | ranking
> --+-
>46 | 30
>45 | 26
>44 | 28
>43 | 25
>42 | 1
>41 | 2
>39 | 3
>38 | 27
>36 | 19
>35 | 18
>34 | 20
>31 | 24
>30 | 17
>29 | 15
>28 | 16
>27 | 12
>26 | 11
>25 | 23
>24 | 21
>23 | 10
>19 | 13
>16 | 9
>12 | 7
>11 | 8
>10 | 29
> 8 | 6
> 7 | 5
> 6 | 14
> 2 | 4
> 1 | 22
> (30 rows)
>
>
> As you can see, the sums are sorted correctly but the ranking is a mess. I
> recongnized that the select seems to follow primarily the internal table
> order. Is there any way to solve this nicely. Hints and solutions are
> appreciated.
>
> Thanks in advance
>
> -Andreas
>
>
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] avoid select expens_expr(col) like unneccessary calculations
Hi, when you have select expensive_expression(column), * from table offset 20 limit 40 can you somehow save the cost for the first 20 calculations of expensive_expression? -- Markus Bertheau. Berlin, Berlin. Germany. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] avoid select expens_expr(col) like unneccessary calculations
On 8 Jul 2003, Markus Bertheau wrote: > when you have > select expensive_expression(column), * from table offset 20 limit 40 > > can you somehow save the cost for the first 20 calculations of > expensive_expression? Right now the only way I can think of that might work is to push the offset/limit into a subselect on table and then do the expensive_expression at the top level. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] avoid select expens_expr(col) like unneccessary calculations
On 8 Jul 2003, Markus Bertheau wrote: > Hi, > > when you have > select expensive_expression(column), * from table offset 20 limit 40 > > can you somehow save the cost for the first 20 calculations of > expensive_expression? Might a functional or partial index work here? ---(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] Database Upgrade scripts (AKA Conditional SQL execution)
Moving to Postgres from MS SQL server wherever I can. When writing database upgrade scripts, it is nice to construct the script so it will run correctly even if run twice. In MS-SQL's TSQL I would do something like this: IF( SELECT COUNT(*) FROM sysobjects WHERE name = 'foo' AND type ='U' ) < 1 THEN CREATE TABLE foo() END Can I accomplish this with postgresql without involving an external process (like say perl)? I guess I could put the upgrade stuff into PL/SQL functions and just drop the functions when I'm done, but I was hoping for something a little "cleaner". -- Richard Rowell <[EMAIL PROTECTED]> signature.asc Description: This is a digitally signed message part
