Did you try DISTINCT ON?
postgres=# table x;
id | qid | uid
+-+
1 | 25 | 1
2 | 25 | 1
3 | 25 | 1
4 | 26 | 1
5 | 26 | 1
6 | 27 | 1
7 | 27 | 1
8 | 25 | 2
9 | 25 | 2
10 | 25 | 2
11 | 26 | 2
12 | 26 | 2
1
On 12/17/2016 07:25 AM, Arup Rakshit wrote:
Hi,
Here is a sample data from table "quiz_results":
id | question_id | user_id
+-+
2 | 25 | 5142670086
3 | 26 |
4 | 26 |
5 | 27 |
6 | 25 | 5142670086
7 | 2
On Sat, Dec 17, 2016 at 10:25 AM, Arup Rakshit
wrote:
> Hi,
>
> Here is a sample data from table "quiz_results":
>
> id | question_id | user_id
> +-+
> 2 | 25 | 5142670086
> 3 | 26 |
> 4 | 26 |
> 5 | 27 |
> 6 | 25
Hi,
Here is a sample data from table "quiz_results":
id | question_id | user_id
+-+
2 | 25 | 5142670086
3 | 26 |
4 | 26 |
5 | 27 |
6 | 25 | 5142670086
7 | 25 | 5142670086
8 | 25 | 5142670086
> There is nothing wrong with LATERALs, they just have no business being
> used here. Sorry for the noise.
Ah. No trouble. In fact I'm glad you chimed in -- it motivated me to
learn about laterals so now I know some new SQL syntax!
--
Sent via pgsql-general mailing list (pgsql-general@postgres
On 04/30/2014 01:08 PM, David Noel wrote:
>> For 9.3, you can write that as:
>>
>> select p.*, s.NoOfSentences
>> from page p,
>> lateral (select count(*) as NoOfSentences
>> from sentence s
>> where s."PageURL" = p."URL") s
>> where "Classification" like case ... e
On 30 Duben 2014, 10:46, David Noel wrote:
> Very strange. I ran the query and it seemed slow so I rewrote it with
> a join instead. Using join it finished in 800ms. The query using the
> lateral finished in more than a minute. I guess I need to do some
> analysis on those queries to figure out why
> For 9.3, you can write that as:
>
> select p.*, s.NoOfSentences
> from page p,
> lateral (select count(*) as NoOfSentences
> from sentence s
> where s."PageURL" = p."URL") s
> where "Classification" like case ... end
> order by "PublishDate" desc
> limit 100;
>
>
The FreeBSD system is running 9.3, the Windows systems are running
9.2. I am waiting on the output from the other developer.
On Tue, Apr 29, 2014 at 8:46 AM, Tom Lane wrote:
> David Noel writes:
>> Both queries are run from a Java project using the latest JDBC driver.
>> The PostgreSQL Server ve
Very strange. I ran the query and it seemed slow so I rewrote it with
a join instead. Using join it finished in 800ms. The query using the
lateral finished in more than a minute. I guess I need to do some
analysis on those queries to figure out why there was such a vast
difference in performance. %
David Noel writes:
> Both queries are run from a Java project using the latest JDBC driver.
> The PostgreSQL Server versions it's being run on are 9.2 and 9.3. The
> query executes and returns just fine when run on a FreeBSD-based
> platform, but executes forever when run under Windows.
Um .. whi
> Is both server/client running on FreeBSD or Windows, or are you switching
> only part of the stack?
When I run it it's all FreeBSD. When the other developer working on it
runs it it's all Windows.
> It shouldn't get stuck. It might be slower on some platforms, but it
> shouldn't really get stuc
> But there are two of them : ' and ' makes ''. If you use only one
> psql/parser will complain.
Ha! Wow. That one totally flew by me. It's not a double quotation mark
(one character), it's a double _single_ quotation mark (two
characters). Yeah, that makes complete sense. Wow. Can't believe I
mis
On 29 Duben 2014, 8:59, David Noel wrote:
> The query I'm running is:
>
> "select page.*, coalesce((select COUNT(*) from sentence where
> sentence."PageURL" = page."URL" group by page."URL"), 0) as
> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>"
> THEN " ELSE '%' END ORDER BY "
On 29/04/2014 12:54, David Noel wrote:
'health'<>'' (if that is what you have) means a boolean expression that
compares the
literal 'health' with the empty literal '' which is of course always false.
Ah. Gotcha. Thanks. I didn't know you could use a single double
quotation mark in a query -- I t
> 'health'<>'' (if that is what you have) means a boolean expression that
> compares the
> literal 'health' with the empty literal '' which is of course always false.
Ah. Gotcha. Thanks. I didn't know you could use a single double
quotation mark in a query -- I thought like in most languages that
On 29/04/2014 12:39, David Noel wrote:
Ehh, to clarify I'm referring to the lone _double_ quotation mark at
the end of the condition 'health'<>''. I called it a "single quotation
mark" because it was a quotation mark all by itself, but realize that
could be misread. Single quotation marks are tec
Ehh, to clarify I'm referring to the lone _double_ quotation mark at
the end of the condition 'health'<>''. I called it a "single quotation
mark" because it was a quotation mark all by itself, but realize that
could be misread. Single quotation marks are technically this: '
Sorry for the newbie sp
> select p.*, s.NoOfSentences
> from page p,
> lateral (select count(*) as NoOfSentences
> from sentence s
> where s."PageURL" = p."URL") s
> where "Classification" like case ... end
> order by "PublishDate" desc
> limit 100;
Great. Thanks so much!
Could I make it
On 04/29/2014 09:44 AM, David Noel wrote:
> Ahh, sorry, copied the query over incorrectly. It should read as follows:
>
> select page.*, coalesce((select COUNT(*) from sentence where
> sentence."PageURL" = page."URL" group by page."URL"), 0) as
> NoOfSentences from page WHERE "Classification" LIKE
On 4/29/2014 12:42 AM, David Noel wrote:
Ok, thanks for the heads up. It confused me, too. It's code I'm just
picking up from another developer, so I don't know why it was done the
way it was done. I'm not super proficient with SQL but I'll take a
stab at rewriting it.
wild guess says it was ba
Ahh, sorry, copied the query over incorrectly. It should read as follows:
select page.*, coalesce((select COUNT(*) from sentence where
sentence."PageURL" = page."URL" group by page."URL"), 0) as
NoOfSentences from page WHERE "Classification" LIKE CASE WHEN
'health'<>'' THEN 'health' ELSE '%' END O
On 4/29/14, Achilleas Mantzios wrote:
> On 29/04/2014 09:59, David Noel wrote:
>> "select page.*, coalesce((select COUNT(*) from sentence where
>> sentence."PageURL" = page."URL" group by page."URL"), 0) as
>> NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>"
>> THEN " ELSE '%' EN
On 29/04/2014 09:59, David Noel wrote:
The query I'm running is:
"select page.*, coalesce((select COUNT(*) from sentence where
sentence."PageURL" = page."URL" group by page."URL"), 0) as
NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>"
THEN " ELSE '%' END ORDER BY "PublishDate"
The query I'm running is:
"select page.*, coalesce((select COUNT(*) from sentence where
sentence."PageURL" = page."URL" group by page."URL"), 0) as
NoOfSentences from page WHERE "Classification" LIKE CASE WHEN "<>"
THEN " ELSE '%' END ORDER BY "PublishDate" DESC Offset 0 LIMIT 100"
I can post the
On 3/20/2014 4:45 PM, Raymond O'Donnell wrote:
On 20/03/2014 07:48, Bhim Kumar wrote:
Hi sir
Currently I am using following query on mysql :
* SHOW TABLES; ---> To get list of table in a particular db.
Assuming you're using psql, did you take the advice you're given on
connection and type
On 20/03/2014 07:48, Bhim Kumar wrote:
> Hi sir
>
> Currently I am using following query on mysql :
>
> * SHOW TABLES; ---> To get list of table in a particular db.
Assuming you're using psql, did you take the advice you're given on
connection and type "help"?
Anyway, to see a list of tables
Hi sir
Currently I am using following query on mysql :
* SHOW TABLES; ---> To get list of table in a particular db.
*
SELECT TABLE_NAME, CONSTRAINT_NAME, COLUMN_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME from
information_schema.key_column_usage WHERE constraint_schema
='myDBName';
On Feb 5, 2012, at 10:46 PM, Tom Lane wrote:
> Drop the parentheses in the GROUP BY.
I had the suspicion that it was some kind of a late-night brain fart ;-)
I don't know where the hell the parens came from, since I've *NEVER* put
spurious parens in a group by clause before. But it took someone
Scott Ribe writes:
> Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work?
> The query:
> select t0."ICD9", t0."Description", count(*)
> from (select distinct "Person_Id", "ICD9", "Description" from
> "PatientDiagnoses") as t0
> group by (t0."ICD9", t0."Description")
>
Is this a bug (in 9.0.4), or have I just gone cross-eyed from too much work?
The query:
select t0."ICD9", t0."Description", count(*)
from (select distinct "Person_Id", "ICD9", "Description" from
"PatientDiagnoses") as t0
group by (t0."ICD9", t0."Description")
order by count(*) desc limit 10;
On 7/6/2010 3:06 AM, GrGsM wrote:
Hi all
I am using the following query for data to be displayed in crosstab :
SELECT closedate,status,
SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
On Tue, Jul 06, 2010 at 12:06:06AM -0700, GrGsM wrote:
> SELECT closedate,status,
>SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
>SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
>SUM (CASE WHEN empcode = 'NT-050' THEN 1 ELSE 0
GrGsM, 06.07.2010 09:06:
Now i need a column in the same result of the query which shows the
difference between the two columns .
For Example :
the result shoud be
Closedate , status , NT028, NT031, NT050,NT062 , NT028-NT031
Please note the last column in bold, i need the difference .
Alr
Hi all
I am using the following query for data to be displayed in crosstab :
SELECT closedate,status,
SUM (CASE WHEN empcode = 'NT-028' THEN 1 ELSE 0 END) AS NT028,
SUM (CASE WHEN empcode = 'NT-031' THEN 1 ELSE 0 END) AS NT031,
SUM (CASE WHEN empcode = 'NT-0
"Michael Thorsen" <[EMAIL PROTECTED]> writes:
> ... I gave a simple example above, but the query runs over 2 tables
> with about a million entries in each. So I am unable to verify what is
> wrong, but I know the count is incorrect as I should not have more than what
> is in the user_table.
You co
On 22/11/2008 16:07, Michael Thorsen wrote:
> For the most part yes. The price and value were "real" columns,
> otherwise the rest of it is the same. On a small data set I seem to get
That's almost certainly the problem, so - rounding errors are causing
the equality test in the join to fail. You s
On 22/11/2008 04:33, Michael Thorsen wrote:
> select count(*)
> from user_table u, locations l
> where u.user_code = l.code
> and u.price = l.price
> and u.value = l.value;
>
> The answer to this should be 2, but when I run my query I get 4 (in fact
Are you sure that's the query that'
I am running the Postgres(8.2.11) on Windows.
I have 2 tables, one with users and one with locations.
user_table
---
user_id user_code price value
1 245.23 -97.82
2 3 42.67 -98.32
3
Hello All:
Ashish wants latest output_date but less than now()
>want to retrieve all the output_id which are having latest
>output_date but less than now() (no future output_dates)
The query written by Tirnath will return count of all output ids given
parent id where output_date is less than no
Hi ,
Try this out
SELECT count(o.id)
FROM output_table o , parent_table p
WHERE o.pid=p.pid AND o_date < now()
GROUP BY p.pid ;
On Thu, 13 Dec 2007 10:00:56 + (GMT)
Ashish Karalkar <[EMAIL PROTECTED]> wrote:
> Hello List member,
> I have a table which ha sfollowing structure
>
> my_t
On Wed, Dec 05, 2007 at 11:43:08AM +,
Steve Grey <[EMAIL PROTECTED]> wrote
a message of 153 lines which said:
> First work out the maximum number of times each value of X will occur in the
> table
A better solution, when you do not know this maximum number, is CREATE
AGGREGATE (http://www
am Wed, dem 05.12.2007, um 3:46:26 -0800 mailte David Fetter folgendes:
> Use the array_accum aggregate from the docs as follows:
>
> SELECT x, array_to_string(array_accum(y),':')
> FROM your_table
> GROUP BY x;
Yes, no noubt a better solution as my new aggregat...
Andreas
--
Andreas Kretsch
On Wed, Dec 05, 2007 at 10:24:04AM +, Ashish Karalkar wrote:
> Hello List member,
>
> Iha a table containing two columns x and y . for single value of x there are
> multiple values in y e.g
>
> XY
>
> 1ABC
> 2PQR
> 3 XYZ
> 4 LMN
> 1
Hi,
Its not elegant, and certainly not dynamic or the perfect solution or for
anything but a static dataset but I've approached this in SQL before as...
First work out the maximum number of times each value of X will occur in the
table - something like "select max(subfoo.ycount) from (select foo.
am Wed, dem 05.12.2007, um 10:47:44 + mailte Ashish Karalkar folgendes:
> > X Y
> >
> > 1 ABC
> > 2 PQR
> > 3 XYZ
> > 4 LMN
> > 1 LMN
> > 2 XYZ
> >
> > I want a query that will give me following output
> >
> > 1 ABC:LMN
> > 2 PQR
"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am Wed, dem 05.12.2007, um 10:24:04
+ mailte Ashish Karalkar folgendes:
> Hello List member,
>
> Iha a table containing two columns x and y . for single value of x there are
> multiple values in y e.g
>
> X Y
>
> 1 ABC
> 2
am Wed, dem 05.12.2007, um 10:24:04 + mailte Ashish Karalkar folgendes:
> Hello List member,
>
> Iha a table containing two columns x and y . for single value of x there are
> multiple values in y e.g
>
> X Y
>
> 1 ABC
> 2 PQR
> 3 XYZ
> 4 LMN
> 1
Hello List member,
Iha a table containing two columns x and y . for single value of x there are
multiple values in y e.g
XY
1ABC
2PQR
3 XYZ
4 LMN
1 LMN
2 XYZ
I want a query that will give me following output
1ABC:LMN
2
Stefan Schwarzer skrev:
> Hi there,
>
> I want to calculate per Capita values on-the-fly, taking for example the
> "Total GDP" data set and divide it by "Total Population". Now, each of
> these data sets have a couple of "0" or "-" values (the latter being
> the indicator for : "no data availa
On 9/5/07, Stefan Schwarzer <[EMAIL PROTECTED]> wrote:
> Hi there,
>
> I want to calculate per Capita values on-the-fly, taking for example
> the "Total GDP" data set and divide it by "Total Population". Now,
> each of these data sets have a couple of "0" or "-" values (the
> latter being the i
On Wed, Sep 05, 2007 at 11:15:43AM +0200, Stefan Schwarzer wrote:
> SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS
> y_2004, countries_view.name AS name
> FROM pop_total, countries_view
> LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
> WHERE pop_total.y_2004
Hi there,
I want to calculate per Capita values on-the-fly, taking for example
the "Total GDP" data set and divide it by "Total Population". Now,
each of these data sets have a couple of "0" or "-" values (the
latter being the indicator for : "no data available").
Until now I have it
Another way is to use correlated-subqueries (refrencing outer
query's columns inside a subquery; hope this feature is supported):
select *
fromFileVersionHistory H1
where modificationDate = ( select max(modificationDate)
fromFileVersionHistory H2
On Sat, Jun 17, 2006 at 16:50:59 -0700,
Kevin Jenkins <[EMAIL PROTECTED]> wrote:
> For example
>
> filename date revision
> file110/05/06 1
> file110/05/07 2
> file210/05/08 1
>
> I want to do a query that will return the greatest date for each
> unique filename
If the revisi
On Jun 18, 2006, at 8:50 , Kevin Jenkins wrote:
I have a beginner question. I have a table with a bunch of
filenames and each of them have a date. Multiple files may have
the same name. For example
filename date revision
file110/05/06 1
file110/05/07 2
file210/05/08 1
I
Hi!
First I want to say thanks for writing PostgreSQL. It's nice to have
a free alternative.
I have a beginner question. I have a table with a bunch of filenames
and each of them have a date. Multiple files may have the same name.
For example
filename date revision
file110/05/0
On Friday 11 February 2005 13:39, Bruno Wolff III wrote:
> On Fri, Feb 11, 2005 at 11:07:24 +,
>
> David Goodenough <[EMAIL PROTECTED]> wrote:
> > I thought of using an inner select for the join, and using limit 1 to
> > get just the one, and forcing the order by to give me the billing
> > ad
On Friday 11 February 2005 11:41, Janning Vygen wrote:
> Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
> > I have an address table, with all the normal fields and a customer name
> > field and an address type. There is a constraint that means that the
> > combination of customer and
On Friday 11 February 2005 11:46, Richard Huxton wrote:
> David Goodenough wrote:
> > I realise this is not strictly a Postgreslql question, but if the best
> > way to solve it involves using PG extensions, such as the PG procedural
> > languages I am only going to do this on PG and so I am happy t
On Friday 11 February 2005 11:31, Matt K wrote:
> David Goodenough wrote:
> >I could do this by doing a select * from addresses where customer = ?
> >and type = 'billing', looking to see if there is a result row and if not
> >repeating the query with type = 'default', but that seems inelegant to
>
On Fri, Feb 11, 2005 at 11:07:24 +,
David Goodenough <[EMAIL PROTECTED]> wrote:
>
> I thought of using an inner select for the join, and using limit 1 to
> get just the one, and forcing the order by to give me the billing
> address by preference, but I am then dependant on the sort order
>
David Goodenough wrote:
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a
Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
>
> I have an address table, with all the normal fields and a customer name
> field and an address type. There is a constraint that means that the
> combination of customer and type have to be unique. Normally the
> only record per cust
David Goodenough wrote:
I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to
me.
Use NULL to indicate that the customer type is defa
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a customer name
field and
Am Donnerstag, 3. Februar 2005 08:32 schrieb Uwe C. Schroeder:
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm going to ask anyways.
> I have a table like
>
> id int4
> user_id int4
> photo varchar
> image_type char(1)
>
> where image_type is either G or X
> What I
# [EMAIL PROTECTED] / 2005-02-02 23:32:28 -0800:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
>
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm going to ask anyways. I have a table like
>
> id int4
> user_id int4
> photo varchar
> image_type char(1)
>
>
Hi Uwe,
I did a solution for you using PLPgSQL,
create or replace function countem() returns varchar as $$
declare
gcount integer;
xcount integer;
result varchar;
begin
select count(*) into gcount
from pix where image_type = 'G';
select count(*) into xcount
you're right it's late, i better to get to bed myself, i forgot to
throw in the parameter for the user_id in there, i'm sure you can
figure that one out.
regards,
-
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:
---
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Maybe it's to late for me to think correctly (actually I'm sure of that). I'm
going to ask anyways.
I have a table like
id int4
user_id int4
photo varchar
image_type char(1)
where image_type is either G or X
What I want to do is have ONE query that
Hi all,
I have 2 tables, with a 1-n relation:
parent( oid, parent_name)
child(oid, child_name, iod_parent)
How do I get the parent_names of all parents without a child?
select parent_name from parent
left join child on (parent.oid=child.iod_parent)
where child.oid is null;
or
select parent_name
Hi all,
I have 2 tables, with a 1-n relation:
parent( oid, parent_name)
child(oid, child_name, iod_parent)
How do I get the parent_names of all parents without a child?
TIA
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-36084
On Wed, 2004-09-08 at 17:13 +0100, Richard Huxton wrote:
> mike wrote:
> > Hi
> >
> > I am trying to work out if this is possible in a select query
> >
> > I have a group by query which could result in several rows, what I want
> > to do is do a text equivalent of a sum() eg:
> >
> > SELECT sum(
mike wrote:
Hi
I am trying to work out if this is possible in a select query
I have a group by query which could result in several rows, what I want
to do is do a text equivalent of a sum() eg:
SELECT sum(inv_id),date,cust
from invoice
group by date,cust
Is there any way to get to a single concat
Hi
I am trying to work out if this is possible in a select query
I have a group by query which could result in several rows, what I want
to do is do a text equivalent of a sum() eg:
SELECT sum(inv_id),date,cust
from invoice
group by date,cust
Is there any way to get to a single concatenated in
On Mon, 1 Mar 2004, Alexander Cohen wrote:
> What sql query can i use to get all relative data to the types of
> fields that a table contains?
select column_name,data_type from information_schema.columns where
table_name='test';
works in 7.4
---(end of broadcast)-
thanx this was the problem : the null values
- Original Message -
From: "Stephan Szabo" <[EMAIL PROTECTED]>
To: "Najib Abi Fadel" <[EMAIL PROTECTED]>
Cc: "generalpost" <[EMAIL PROTECTED]>
Sent: Monday, November 10, 2003 07:58 PM
Su
Hi if you take a look at the following
query's you can see that the third query should logically
return 043219but instead it's not returning any rows ?? What's
happening ??
dragon=> SELECT cod_etu from parcours_v where
cod_etu = '043219'; cod_etu-(0 rows)
dragon=> S
> fine). PG returns: ERROR: Relation "_con" does not exist
>
> This is my query:
>
> SELECT
> _CON.con_id,
Please make sure you get the quoting right regarding table
names. PostgreSQL will fold _CON into _con unless quoted
"_CON". So, it may be that you created the table with quotes
("_CON").
At 14:58 07/12/00 -0500, Travis Bauer wrote:
>How would I write an sql statement which would select all the
>records from a table where some string field contains
>some substring? Example: find all the records where f1 contains
>the world "cool."
select * from table where f1 ~ 'cool';
or case i
On Thu, 7 Dec 2000, Travis Bauer wrote:
> How would I write an sql statement which would select all the
> records from a table where some string field contains
> some substring? Example: find all the records where f1 contains
> the world "cool."
SELECT * FROM mytable WHERE f1 LIKE '%cool%';
or
82 matches
Mail list logo
