I'm reading Joe Celko's book SQL Programming Style for the second time
and although I've been an OO developer for quite a few years I'm
fairly green wrt SQL. Joe is obviously something of a curmudgeon and I
would fall squarely into his newbie OO developer ordinal scale and I'm
trying to avoid the
Hello,
I have a table with cca 400,000 rows. The table contains column "key"
of varchar(20) type containing 10 distinct values. I want to get out
what distinct values are present in the column. I use this simple
query, which is very slow:
SELECT DISTINCT Key FROM MRTPContactValue
Here is the
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in the table and fits the
> criteria for a key
Miroslav ?ulc wrote:
> Hello,
>
> I have a table with cca 400,000 rows. The table contains column "key" of
> varchar(20) type containing 10 distinct values. I want to get out what
> distinct values are present in the column. I use this simple query,
> which is very slow:
>
> SELECT DISTINCT Key F
Well, "key" is not primary key from another table. It is just a column
in pair "key" => "value".
The structure of the table is this:
Id (primary key)
MRTPContactId (id of contact from table MRTPContact)
Key (key from pair key => value)
Value (value from pair key => value)
So I want the get th
Miroslav ?ulc wrote:
> Well, "key" is not primary key from another table. It is just a column
> in pair "key" => "value".
> The structure of the table is this:
>
> Id (primary key)
> MRTPContactId (id of contact from table MRTPContact)
> Key (key from pair key => value)
> Value (value from pair ke
It might be a good solution :-)
Thank you for your help.
Miroslav Šulc
Alvaro Herrera napsal(a):
> Miroslav ?ulc wrote:
>
>> Well, "key" is not primary key from another table. It is just a column
>> in pair "key" => "value".
>> The structure of the table is this:
>>
>> Id (primary key)
>> MR
=?ISO-8859-2?Q?Miroslav_=A9ulc?= <[EMAIL PROTECTED]> writes:
> I have a table with cca 400,000 rows. The table contains column "key" of
> varchar(20) type containing 10 distinct values. I want to get out what
> distinct values are present in the column. I use this simple query,
> which is very slow
The GROUP BY is really fast :-)
Thank you.
Miroslav Šulc
Tom Lane napsal(a):
> Try
> SELECT Key FROM MRTPContactValue GROUP BY Key
>
> The "select distinct" code is a bit old and crufty, GROUP BY is usually
> smarter.
>
> regards, tom lane
>
begin:vcard
fn;quoted-
"David Clarke" <[EMAIL PROTECTED]> writes:
> is it really that big an issue these days to have a 100 character primary
> key? Are there postgres-specific implications for either approach?
It's exactly the same size issue as ever. A 20% increase in space usage is a
20% performance hit in certain
Miroslav ?ulc wrote:
> The GROUP BY is really fast :-)
Doh! How does it do it?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
---(end of broadcast)---
TIP 1: if posting
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Miroslav ?ulc wrote:
> > Well, "key" is not primary key from another table. It is just a column
> > in pair "key" => "value".
> > The structure of the table is this:
> >
> > Id (primary key)
> > MRTPContactId (id of contact from table MRTPContact)
> >
Greg Stark napsal(a):
Actually you could try the equivalent query:
SELECT Key FROM MRTPContactValue GROUP BY Key
This may or may not be faster because it can use a hash aggregate plan. I
would expect it to be faster here because there are few distinct keys and the
planner predicts that.
On Thu, 01 Jun 2006, David Clarke wrote:
> So I'm designing a table and I'm looking for an appropriate key. The
> natural key is a string from a few characters up to a maximum of
> perhaps 100. Joe gets quite fierce about avoiding the use of a serial
> id column as a key. The string is unique in t
I am having some serious mental block here. Here is the abstract
version of my problem. I have a table like this:
unique_id (PK) broadcast_id date_sent status
1 1 2005-04-0430
2 1 2005-04-01 30
3
I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my
PKEY's -- as an absolute rule -- I guess I am a purist... Everything else
(the other columns) can have unique constraints, etcetera and be FOREIGN
KEYS, etc...
Try INSERTING your 100 character "natural" key into a table wi
Collin Peters wrote:
I am having some serious mental block here. Here is the abstract
version of my problem. I have a table like this:
unique_id (PK) broadcast_id date_sent status
1 1 2005-04-0430
2 1
select my_sub.max_date, broadcast_history.statusfrom (SELECT MAX(date_sent) max_date, broadcast_idFROM broadcast_historyGROUP BY broadcast_id) my_subinner join broadcast_history on (broadcast_history.broadcast_id = my_sub.broadcast_id
and broadcast_history.date_sent = my_sub.max_date);This should w
What I would like to do is simply get the last date_sent and it's
status for every broadcast. I can't do a GROUP BY because I can't put
an aggregate on the status column.
SELECT MAX(date_sent), status
FROM broadcast_history
GROUP BY broadcast_id
You could try the following:
select status
fro
select max(date_sent) from table;
would equal
select date_sent from broadcast_history order by date_sent DESC limit 1;
That sounds like a hack. Is limit a SQL-99 standard? Is there are
another way to do this?
Sorry to take over your topic, Collin.
Thanks,
Yasir
---(e
* Alvaro Herrera:
> Miroslav ?ulc wrote:
>> The GROUP BY is really fast :-)
>
> Doh! How does it do it?
It uses a hash table and can therefore discard duplicate rows more
quickly (essentially linear time in the number of rows if the number
of different rows is bounded).
"Collin Peters" <[EMAIL PROTECTED]> writes:
> What I would like to do is simply get the last date_sent and it's
> status for every broadcast. I can't do a GROUP BY because I can't put
> an aggregate on the status column.
You missed the key idea about how to use DISTINCT ON.
SELECT DISTINCT ON (e
It is a hack, but when someone wants you to do something in a way
different from the norm, aren't they asking for a hack?
SQL Server does something like
select top (1) from
I am thinking this is NOT a SQL-99 standard.
-Aaron
On 6/1/06, Yasir Malik <[EMAIL PROTECTED]> wrote:
> select max(
It is a hack, but when someone wants you to do something in a way
different from the norm, aren't they asking for a hack?
SQL Server does something like
select top (1) from
I am thinking this is NOT a SQL-99 standard.
This was an interview with Amazon, and I don't think Amazon wanted a hac
Yasir Malik wrote:
> >It is a hack, but when someone wants you to do something in a way
> >different from the norm, aren't they asking for a hack?
> >
> >SQL Server does something like
> >select top (1) from
> >
> >I am thinking this is NOT a SQL-99 standard.
> >
> This was an interview with A
On Thu, 2006-06-01 at 14:47, Yasir Malik wrote:
> > It is a hack, but when someone wants you to do something in a way
> > different from the norm, aren't they asking for a hack?
> >
> > SQL Server does something like
> > select top (1) from
> >
> > I am thinking this is NOT a SQL-99 standard.
"codeWarrior" <[EMAIL PROTECTED]> writes:
> I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my
> PKEY's -- as an absolute rule -- I guess I am a purist... Everything else
> (the other columns) can have unique constraints, etcetera and be FOREIGN
> KEYS, etc...
>
> Try INSER
Collin,
I have a similar circumstance in one of my own apps. I operate under
the simple presumption that the unique_id is sequential and thus the
record with the highest unique_id is the most recent entry. In that
case I use a query such as
select *
from broadcast_history
where unique_id in (
On Thu, 2006-06-01 at 14:13 -0400, Yasir Malik wrote:
> > What I would like to do is simply get the last date_sent and it's
> > status for every broadcast. I can't do a GROUP BY because I can't put
> > an aggregate on the status column.
> >
> > SELECT MAX(date_sent), status
> > FROM broadcast_hist
hi all, i posted this problem on the novice thread,
but it makes much more sense to post it here, instead.
sorry fo rthe double posting, i'll be sure to post
advanced SQL questions here in the future.
i have the following two tables (trimmed down for
simplicity's sake):
t_inspect
id, inspect_tim
On Thu, 01 Jun 2006, Chris Browne wrote:
> Celko is decidedly *NOT* promoting the notion that you should use a
> 100 byte long "natural key."
>
> Jamie's comments of "Orthodox versus Reform" seem reasonably
> appropriate in outlining something of the difference between the
> positions.
Just to
On Thu, Jun 01, 2006 at 04:09:21PM -0700, [EMAIL PROTECTED] wrote:
> what i can't seem to do is to get both - a count of
> the total number of t_inspect_result.inspect_pass
> where the value is true and a total count, by unique
> t_inspect.id.
Are you looking for something like this?
SELECT 1.0 *
> On Thu, Jun 01, 2006 at 04:09:21PM -0700,
> [EMAIL PROTECTED] wrote:
> > what i can't seem to do is to get both - a count
> of
> > the total number of t_inspect_result.inspect_pass
> > where the value is true and a total count, by
> unique
> > t_inspect.id.
>
> Are you looking for something like
On 6/2/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
On Thu, 01 Jun 2006, Chris Browne wrote:
> Celko is decidedly *NOT* promoting the notion that you should use a
> 100 byte long "natural key."
>
> Jamie's comments of "Orthodox versus Reform" seem reasonably
> appropriate in outlining someth
Is this SQL-99 compliant or a PostgreSQL specific query? I really like it and have never seen this before.-AaronOn 6/1/06, Tom Lane <
[EMAIL PROTECTED]> wrote:"Collin Peters" <
[EMAIL PROTECTED]> writes:> What I would like to do is simply get the last date_sent and it's> status for every broadcast
"Aaron Bono" <[EMAIL PROTECTED]> writes:
> Is this SQL-99 compliant or a PostgreSQL specific query? I really like it
> and have never seen this before.
DISTINCT ON is a Postgres-ism, I'm afraid. It's pretty handy though.
regards, tom lane
---(end
36 matches
Mail list logo
