- Original Message -
From: "Wes James"
To:
Sent: Friday, June 04, 2010 2:30 PM
Subject: Re: [SQL] sum an alias
On Thu, Jun 3, 2010 at 11:54 PM, A. Kretschmer
wrote:
In response to Wes James :
In the statement:
select
MAX(page_count_count) - MIN(page_count_count) as day_tot,
MAX(
Howdy, Aaron,
For me this is not an easy question, specially when I don't have your data
here on my PC to test.
But as a first approach, try this query. It is designed to give you the
oldest 5 entries.
But, be aware that this is non-tested code. Be prepared for it to not work
or even to cont
Howdy, Cedric .
Before analysing this problem of yours further, I 'd like you to kindly
clarify me some points, please
I have table following tables T0,T1,T2,T3, T1T2
with
-T0(id, fk_T1)
-T1(id )
-T2(id, fk_T3)
-T1T2(fk_T1,fk_T2)
-T3 (id)
The table T0 represents sets of T1 items.
The tab
Oliveiros d'Azevedo Cristina wrote:
Howdy, Cedric .
Before analysing this problem of yours further, I 'd like you to
kindly clarify me some points, please
I have table following tables T0,T1,T2,T3, T1T2
with
-T0(id, fk_T1)
-T1(id )
-T2(id, fk_T3)
-T1T2(fk_T1,fk_T2)
-T3 (id)
Can you specify a little more how you represent T1 item sets with this
data model?
(T0.id,T0.fk_T1)=(1,234),(1,235),(1,236)
234,235,236 all T1 items and 1 the set of them
What exactly represents one row from table T0? A pair (id, fk_T1) ? A
set with just one element?
one item in the set.
Th
Howdy, Rainer.
Please advice me,
The dates always follow that sequential pattern?
Or can be holes on the dates sequence?
Best,
Oliveiros
- Original Message -
From: "Rainer Stengele"
To:
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets
Hi,
having a table si
Howdy, Rainer.
It's been a while, so I don't know if you are still interested in this
problem or if you, in the meantime, found yourself a solution,
but I've tried this on a local copy of the example you provided and it
seems to work.
The problem is that I suspect that if you have several t
e day with several sets?
Best,
Oliveiros
- Original Message -
From: "Rainer Stengele"
Newsgroups: gmane.comp.db.postgresql.sql
To: "Oliveiros d'Azevedo Cristina"
Cc: <>
Sent: Thursday, July 29, 2010 10:41 AM
Subject: Re: grouping subsets
Howdy Cristina,
From: "Rainer Stengele"
To: "Oliveiros d'Azevedo Cristina"
Cc:
Sent: Thursday, July 29, 2010 1:10 PM
Subject: Re: grouping subsets
No. This is by accident.
We have to assume that the combinations do change anytime, and many times
per day.
So
"Or is it possible
sql.sql
To: "Oliveiros d'Azevedo Cristina"
Cc:
Sent: Friday, July 30, 2010 10:35 AM
Subject: Re: grouping subsets
the table may include up to maybe 30 entries per day, average maybe 10-15
After a year this makes about 10.000 entries - maximum, average about 5000
entries.
For the
Howdy!
At any rate, say I have 3 tables:
table plant
id:integer
name string
table seed_supplier
id: integer
company_name: string
table plant_seed_supplier
plant_id
seed_supplier_id
plant_seed_supplier is a join table that supports a many to many
relationship between the plant table an
Howdy, Gordon.
This query is an attempt to replicate your items table with data just from
changelog table.
Could you please try it out?
Best,
Oliveiros
SELECT itm_id,usr_id_create, itm_date_create, usr_id_modify, itm_date_modify
FROM
(
SELECT a.itm_id, a.usr_id as usr_id_create, create as itm_d
Howdy , Michele,
Give this a try
SELECT id_user
FROM t_your_table
WHERE datetime
BETWEEN A -- plug here beginning of interval
AND B-- and end here
GROUP BY id_user
HAVING COUNT(*) = -SUM(value)
Then tell me if it gives you what you want
Best,
Oliveiros Cristina
- Original Message
time
interval?
Is my understanding correct?
Best,
Oliver
- Original Message -
From: "Michele Petrazzo - Unipex"
To: "Oliveiros d'Azevedo Cristina"
Cc:
Sent: Friday, September 17, 2010 4:45 PM
Subject: Re: [SQL] all the table values equal
Oliveiros d'
It would help if you explain a little better the background of the
problem you're trying to solve.
You want to find all the user IDs which have the same value on a
given time interval?
Is my understanding correct?
Yes.
Re-reading my post I saw that I could explain better!
id_user | value
d the clause "WHERE "value" = x -- x=the
specific value you want to look for
to the query above. It'll return just the id_user(s) that have just the "value"
x.
Please try it out and feel free to get back to me if it didn't work or if it
didn't solve your
Yes. All the users that have only one value into the "value" column and
that value is NN.
Understood. Try the query from the previous mail, adding the clause WHERE
"value" = NN as I did explain.
Or will you need to find all the users that don't change value with just
one query? And the
Howdy, Adrian,
Please see my questions below
snps table
id | sample_id | chromosome | from | to |
1 1chr1 10 11
2 1 chr1 14 15
3 2
Howdy, Adrian
Dunno if this is exactly what you want
SELECT *
FROM
(
SELECT chr,cfrom,cto,count(*) as numberOfDuplicates
FROM t_fairly_large_table
GROUP BY chr,cfrom,cto
) x
NATURAL JOIN t_fairly_large_table y
WHERE numberOfDuplicates > 1
The idea of this (untested) query
is to produce somet
Howdy, Tarlika.
First, did you past correctly your query into your mail?
I am asking this because your query doesn't seem work for me, it returns an
empty list :-|
Your most nested query, this one,
-- distinct trainer id-name
select distinct on (trainer_id,trainer_name)
Hello again,
Tarlika.
In what concerns to indices, I 'm affraid I may not be the best person to
advise you, my knowledge of them hardly goes beyond the most trivial cases.
I'm sure there are plenty of other people on the list who are able to give
you better advise than me.
But, on this query
Hallo Andreas,
I reduced the problem to the innermost query:
1) SELECT DISTINCT trainer_id, trainer_name FROM student
This results in a sequential table scan. Execution time 7500ms.
2) I created an INDEX ON (trainer_id, trainer_name). Then I had an index
scan instead, which still cost 7000ms.
Hi, John.
I am not familiar with the functions Tom's indicated and I'm sure they
constitute a much more straightfoward to solve your problem.
Meanwhile, if you'd like to solve it with just SQL give this a try and see if
it gives you the result you want
Best,
Oliveiros
SELECT product_id, MIN(p
- Original Message -
From: Oliveiros d'Azevedo Cristina
To: John Lister ; [email protected]
Sent: Wednesday, November 17, 2010 4:09 PM
Subject: Re: [SQL] obtaining difference between minimum value and next in
size
Hi, John.
I am not familiar with the func
Salut, Louis-David,
Can you please state the columns belonging to price table
and give a concrete example?
Say, data before and data after you want to do?
Thank you
A bien-tôt,
Oliveiros
- Original Message -
From: "Louis-David Mitterrand"
To:
Sent: Tuesday, November 23, 2010 3:19 P
Howdy, Michelle,
If you write something like this,
SELECT hour , COUNT(id_user) as count
FROM
(
SELECT EXTRACT(hour from my_date) as hour, id_user FROM michelle
GROUP BY EXTRACT(hour from my_date),id_user
)subquery
GROUP BY hour
for each hour it will count the number of distinct user_id's ther
Ciao
* Olá!
for each hour it will count the number of distinct user_id's there
are . If I understood correctly what you need...
Can you please test it and see if it is OK for your needs? With me,
it worked on the sample data you provided
Yes! Simply perfect!
* Great to hear it worked
Could you please
include the full insert SQL statement?
Best,
Oliveiros
- Original Message -
From: "ndias"
To:
Sent: Friday, November 26, 2010 4:22 PM
Subject: [SQL] Insert row in 1.10.1 and 1.10.3
Hello to all,
I have a question regarding the behavior of insert row in the two
Howdy, Tarsis.
Please try this out.
SELECT a.id, id_table1,a.name
FROM "Table2" a
NATURAL JOIN
(SELECT id_table1
"Table2"
GROUP BY id_table1
HAVING COUNT(*) > 1) b
Tell me if it worked or not, and if it didn't the errors/uncorrect results.
Best,
Oliveiros
- Original Message -
From
Hi, Lu Ying.
How do you define which row is #1 ? And #2 ?
E.g. Ordered by lname? Or gname...?
Best,
Oliveiros
- Original Message -
From: "Emi Lu"
To:
Sent: Wednesday, January 26, 2011 4:11 PM
Subject: [SQL] how to get row number in select query
Good morning,
For postgresql 8.3
If it is to order in ascendent fashion by, say, lname,
one possibility would be
SELECT COUNT(b.*) as row_number, a.lname,a.gname
FROM "Table1" a, "Table2" b
WHERE a.lname >= b.lname
GROUP BY a.lname,a.gname
ORDER BY row_number
If you want to order by gname just change the WHERE clause according
Hi Oliveiros,
Howdy!
If it is to order in ascendent fashion by, say, lname,
one possibility would be
SELECT COUNT(b.*) as row_number, a.lname,a.gname
FROM "Table1" a, "Table2" b
WHERE a.lname >= b.lname
GROUP BY a.lname,a.gname
ORDER BY row_number
If you want to order by gname just chang
Doesn't the SELECT you indicated do what you need?
Best,
Oliveiros
- Original Message -
From: "Emi Lu"
To:
Sent: Monday, January 31, 2011 3:50 PM
Subject: [SQL] Get days between two dates?
Good morning,
Is there an existing method to get days between two dates?
For example,
se
That's precisely what I think..
:-|
- Original Message -
From: "Kenneth Marshall"
To: "Emi Lu"
Cc:
Sent: Monday, January 31, 2011 4:39 PM
Subject: Re: [SQL] Get days between two dates?
On Mon, Jan 31, 2011 at 10:50:43AM -0500, Emi Lu wrote:
Good morning,
Is there an existing me
Howdy, Tarlika.
If the data doesn't bring the AM/PM how are you supposed to distinguish
between,say, 10pm and 10am ? Does it always start at 10:30am? So 10am never
arises?
Best,
Oliveiros
- Original Message -
From: "Tarlika Elisabeth Schmitz"
To:
Sent: Tuesday, February 08, 2011
If your table is not terribly big, you can
try something like
SELECT a.col1,a.col2, COUNT(*) as row_number
FROM yourTable a,yourTable b
WHERE a.col1 >= b.col1 -- I'm assuming col1 is primary key
GROUP BY a.col1,a.col2
ORDER BY row_number
This is pure SQL, should work in every version...
Bes
The ts means the time the user started on a project ?
Or the time he finished?
Or can mean both? If so, how do you can tell one from the other? Different
event_type s ?
Is it correct to assume from your words that an user cannot be in more than
one project at the time? If so, can't be overlappin
Howdy, Marcel,
In the example output you provided the ID = 2 should have just one
record...Ain't I right?
Best,
Oliveiros
- Original Message -
From: Jira, Marcel
To: '[email protected]'
Sent: Wednesday, June 15, 2011 4:23 PM
Subject: [SQL] Merge overlapping time-perio
Hello again, Marcel.
I tried this and it seems to work on the example you provided, iif my
understanding is correct and you want the ID=2 to have just one record on final
output.
That makes sense to me because
2 2000-01-15 2000-03-31
2 2000-04-01 2000-04-15
are in direct sequence (I
- Original Message -
From: LALIT KUMAR
To: Oliveiros d'Azevedo Cristina
Hi,
I do have order of rows. The problem originally is: I need to set all flag
with value 0 to -1 whose next row has flag as 1. This is to be done for each
city separetly, i.e. flag of last r
Strange...
Tables have hidden columns but AFAIK, "name" is not one of them...
http://www.postgresql.org/docs/9.0/interactive/ddl-system-columns.html
Best,
Oliveiros
- Original Message -
From: "Nikolay"
To:
Sent: Thursday, August 04, 2011 3:50 PM
Subject: [SQL] Mysterious column "na
How do you access your database?
Through some client application like psql ? Through some other mean?
The query you included is the query you wanna perform against ur table?
Or was it just an example?
Do you wanna avoid having to write the complete query on the command line?
If so, you sound li
Something like this...?
SELECT first_name,surname, email1 || ';' || email2
FROM t_your_table;
Best,
Oliver
- Original Message -
From: "gvim"
To: "pgsql sql"
Sent: Thursday, August 25, 2011 3:21 PM
Subject: [SQL] Add one column to another
I have to deal with a table which contain
Something like
ALTER TABLE t_yourtable DROP CONSTRAINT
and then
ALTER TABLE t_yourtable ADD FOREIGN KEY
?
Best,
Oliveiros
- Original Message -
From: "Emi Lu"
To:
Sent: Friday, October 21, 2011 2:36 PM
Subject: [SQL] how to temporally disable foreign key constraint check
Good m
Howdy, Abhinandan,
A quick and dirty solution might be this :
SELECT *
FROM
(
SELECT a.name,MAX(b.value) as height
FROM original a
LEFT JOIN original b
ON a.name = b.name
AND b.attribute = 'Height'
GROUP BY a.name
) height
NATURAL JOIN
(
SELECT a.name,MAX(b.value) as weigth
FROM original a
LEFT
I have not.
I've already skimmed through it.
Indeed, it is very interesting
Thanx , Scott
Best,
Oliver
- Original Message -
From: "Scott Swank"
To: "Oliveiros d'Azevedo Cristina"
Cc: "Abhinandan Raghavan" ;
Sent: Tuesday, December 06, 2
UPDATE admin
SET parent = SUBSTR(id,1,4);
Doesn't it do what you want?
Best,
Oliveiros
- Original Message -
From: "ssylla"
To:
Sent: Thursday, February 09, 2012 1:58 AM
Subject: [SQL] update column
Dear list,
sorry, I already posted this, but it did not seem to have been accepte
Hi, Gary,
I'm answering by editing your e-mail
__
I have three tables,
users - all users of my web site
facilities - facilities available on my web site
facility_levels - access levels per user/facility.
One of my facilities is a document library (f_id = 22)
For this facility I have the
Hi, Swärd,
As you didn't name your tables' columns I decided to call them col1, col2, etc.
I dunno if this will do what you want as it is completely untested code.
But, give it a try and see if it works and if it doesn't, tell me the error,
and we'll continue from there.
You'll have to substitu
Great to Hear!
Best,
Oliver
- Original Message -
From: Swärd Mårten
To: Oliveiros d'Azevedo Cristina ; [email protected]
Sent: Thursday, March 01, 2012 11:01 AM
Subject: Re: [SQL] Aggregate and join problem
It worked like a charm! Many thanks for that
I'm affraid
you can't.
AFAIK,
WHERE clause is processed before the SELECT output expressions
Best,
Oliveiros
- Original Message -
From: "Marcel Ruff"
To:
Sent: Friday, May 04, 2012 11:25 AM
Subject: [SQL] How change col name during query to use it in where clause
Hi,
is an alias
Gary,
You describe two tables vehicle stock and tax requests. The former has a
one-to-many relationship wit the second one, right?
But your query involves stock details and used_diary.
What is the relationship of these two new tables to the previous ones?
Could you please kindly supply an ex
09:15:31.569471 |
42363 | 2012-05-16
(2 rows)
On Wednesday 23 May 2012 10:37:31 Oliveiros d'Azevedo Cristina wrote:
Gary,
You describe two tables vehicle stock and tax requests. The former has a
one-to-many relationship wit the second one, right?
But your query involves stock details and used_diar
= u.ud_pex_registration
where s_stock_no = 'UL15470';
GROUP s_stock_no,s_regno,s_vin,s_created
) subq
JOIN
used_diary sec
ON subq.m = sec.ud_id
Best,
Oliver
- Original Message -
From: "Oliveiros d'Azevedo Cristina"
To: "Gary Stainburn" ;
Sent: Wednesday, May 23, 20
Hi, Gary,
Unless I'm mistaken this didn't give what you need.
Could you please tell me (if you have time) the error returned or wrong
result, just for my own understanding of where I've gone sideways on
this...?
Best,
Oliver
- Original Message -
From: "Oliveiro
Hi, Mike,
Can you tell me if this gives what you want, and if it doesn't, what is the
error reported, or wrong result ?
This is untested query, so Im not sure about it.
Best,
Oliver
SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2
(
SELECT printers.make, printers.model,
2.make,subquery2. model,
subquery2.color,subquery2.type,subquery1.cpp
ORDER BY make, model;
- Original Message -
From: "Oliveiros d'Azevedo Cristina"
To: "Relyea, Mike" ;
Sent: Friday, June 01, 2012 3:56 PM
Subject: Re: [SQL] Lowest 2 items per
Hi, Mike,
Can you
Oliver,
I had to make a few grammatical corrections on your query to get it to
run, but once I did it gave me almost correct results. It leaves out
all of the printer models that only have one consumable with a cost.
Some printers might have more than two black inks and some might have
only one
I only made grammatical changes necessary for the query to function
(adding a missing FROM, fully qualifying "SELECT Make" as " SELECT
subquery2.Make", etc.)
I tried changing the join type to right and left but that did not have
the desired result.
* I see...
If we add a query with a union tha
With a CASE ... WHEN statement?
Best,
Oliver
- Original Message -
From: "Gary Stainburn"
To:
Sent: Monday, August 20, 2012 1:17 PM
Subject: [SQL] generated dates from record dates - suggestions
Hi folks.
I've got a table with three dates which are populated from an external
sour
Have you tried this
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Best,
Oliveiros
- Original Message -
From: "Salomao Domingos"
To:
Sent: Monday, September 17, 2012 7:58 PM
Subject: [SQL] Disabling constraint check
Hi,
I'm need to import data to PostgreSQL via csv file.
Hi, Scott.
I'd like to kick in this thread to ask you some advice, as you are
experienced in optimizing queries.
I also use extensively joins and unions (less than joins though).
Anyway, my response times are somewhat behind miliseconds, they are situated
on seconds range, and sometimes they ex
62 matches
Mail list logo
