Re: [SQL] value from max row in group by
Sorry, but I never thought. I'm developing this on my server I'm developing it for someone else who wants it in a WordPress / MySQL environment (I don't know MySQL). Would this or something similar work in mysql? (Sorry for going O.T.) On Thursday 25 July 2013 19:53:06 Marc Mamin wrote: > > > >Von: pgsql-sql-ow...@postgresql.org [pgsql-sql-ow...@postgresql.org]" > > im Auftrag von "Venky >Kandaswamy [ve...@adchemy.com] > > > >You can use Postgres WINDOW functions for this in several different ways. > > For example, one way of doing it: > > > >select stts_id, last_value(stts_offset) over (partition by stts_id order > > by stts_offset desc) + last_value(stts_duration) over (partition by > > stts_id order by stts_offset desc) from table > >group by stts_id; > > another simple solution with distinct on: > > select distinct on (stts_id, stts_offset) stts_id, > stts_offset+stts_duration from table > order by stts_id, stts_offset desc > > Marc Mamin > -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unique index and unique constraint
I guess I am understanding that it is possible to set a unique index or a unique constraint in a table, but I cannot fully understand the difference, even though I have Google some articles about it. I will very much appreciate any guidance. Respectfully, Jorge Maldonado
Re: [SQL] Unique index and unique constraint
I try to explain my point of view, also in my not so good English: A primary key is defined by dr. Codd in relational model. The key is used to identify a record. In good practice, you must always define a primary key. Always. The unique constraint will simply say: this value (or combination) should not be found more than one time on this column in this table. So you can say: just a convention? Consider this: If you say unique, you can still accept multiple rows with the same NULL value. This is not true with primary key. You can define multiple unique constraint on a table, but only a primary key. This, and the concept of primary key, can help someone else to read your database. To know in same cases, the logic of the data, and know what identifies a row. That is not simply the same as: not duplicate this value. Luca. 2013/7/26 JORGE MALDONADO > I guess I am understanding that it is possible to set a unique index or a > unique constraint in a table, but I cannot fully understand the difference, > even though I have Google some articles about it. I will very much > appreciate any guidance. > > Respectfully, > Jorge Maldonado >
Re: [SQL] Unique index and unique constraint
JORGE MALDONADO escribió: > I guess I am understanding that it is possible to set a unique index or a > unique constraint in a table, but I cannot fully understand the difference, > even though I have Google some articles about it. I will very much > appreciate any guidance. The SQL standard does not mention indexes anywhere. Therefore, in the SQL standard world, the way to define uniqueness is by declaring an unique constraint. Using unique constraints instead of unique indexes means your code stays more portable. Unique constraints appear in INFORMATION_SCHEMA.TABLE_CONSTRAINTS, whereas unique indexes do not. PostgreSQL implements unique constraints by way of unique indexes (and it's likely that all RDBMSs do likewise). Also, the syntax to declare unique indexes allows for more features than the unique constraints syntax. For example, you can have a unique index that covers only portion of the table, based on a WHERE condition (a partial unique index). You can't do this with a constraint. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Criteria to define indexes
I have a table with fields as follows: * sag_id * sag_header * sag_comments * sag_date * sag_section_id (**) * sag_artist_id (**) * sag_author_id (**) * sag_producer_id (**) As you can see, fields mark with (**) are identifiers that reference another table (catalog of sections, catalog of artists, etc). Firstly, I need an index on "sag_date" because our application has a search option under this criteria. However, there are also search criterias on "sag_section", "sag_artist", "sag_author" and "sag_producer" because, for example, a user may need to get the records of certain artist only. Furthermore, our application offers a user to select several cominations of criterias: * Artist + Author * Artist + Producer * Artist + Author + Producer * Section + Artist. And so on. What I see is that it is not a good decision to set a key for every possibility because it will have an impact on performance due to index maintenance. What would be a good way to define indexes in a case like this? With respect, Jorge Maldonado
Re: [SQL] Criteria to define indexes
JORGE MALDONADO wrote > And so on. What I see is that it is not a good decision to set a key for > every possibility because it will have an impact on performance due to > index maintenance. What would be a good way to define indexes in a case > like this? For your specific case, and also more generally, you will define multiple indexes with a single column within each. PostgreSQL is able to fairly efficiently scan multiple indexes and then combine them to find records that exist on both (or other logical combinations). Multi-key indexes can be advantageous in, for instance, composite primary key definitions but in this kind of star-schema setup simply have each foreign key and whatever other searching fields you require maintain their own individual index. David J. P.S. Arguably, having a separate column for each kind of person is a poor design at face value - though not uncommon. Whether it is going to bite you in the future is unknown but depending on whether a single person can hold multiple roles or if you need to add new roles in the future maintenance and querying this table for summary information may become more difficult. At the same time basic data entry and modelling to some degree is easier since this model is simpler. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Criteria-to-define-indexes-tp5765334p5765336.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index and unique constraint
On Fri, Jul 26, 2013 at 3:19 PM, Alvaro Herrera wrote: > JORGE MALDONADO escribió: >> I guess I am understanding that it is possible to set a unique index or a >> unique constraint in a table, but I cannot fully understand the difference, >> even though I have Google some articles about it. I will very much >> appreciate any guidance. > > The SQL standard does not mention indexes anywhere. Therefore, in the > SQL standard world, the way to define uniqueness is by declaring an > unique constraint. Using unique constraints instead of unique indexes > means your code stays more portable. Unique constraints appear in > INFORMATION_SCHEMA.TABLE_CONSTRAINTS, whereas unique indexes do not. > > PostgreSQL implements unique constraints by way of unique indexes (and > it's likely that all RDBMSs do likewise). Also, the syntax to declare > unique indexes allows for more features than the unique constraints > syntax. For example, you can have a unique index that covers only > portion of the table, based on a WHERE condition (a partial unique > index). You can't do this with a constraint. Also, AFAIU, one can defer the uniqueness check until the end of transaction if it is constraint, and can not it it is unique index. Correct? http://www.postgresql.org/docs/9.2/static/sql-set-constraints.html -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA Profile: http://www.linkedin.com/in/grayhemp Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979 Skype: gray-hemp Jabber: gray...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
