agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedhelp on sql query
7+ messages / 5 participants
[nested] [flat]
* help on sql query
@ 2002-11-26 13:14 Zuev Dmitry <envoy1@chat.ru>
2002-11-26 15:26 ` Re: help on sql query Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Zuev Dmitry @ 2002-11-26 13:14 UTC (permalink / raw)
To: pgsql-sql
Suppose you have a table T:
----------------------
A B
----------------------
1 '111'
2 '222'
----------------------
How do you select A and B of the record with A maximum?
The desirable result therefore is (2, '222')
The two variants I could grow with are:
1) SELECT A, B FROM T ORDER BY A DESC LIMIT 1
2) SELECT A, B FROM T WHERE A IN (SELECT MAX(A) FROM T)
What do yo think of the queries above? And give me the better implementation
if possible.
__________________________________________________________________ Dmitry
Vitalievitch ICQ#: 11000008 Current ICQ status: + More ways to contact me
__________________________________________________________________
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: help on sql query
2002-11-26 13:14 help on sql query Zuev Dmitry <envoy1@chat.ru>
@ 2002-11-26 15:26 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2002-11-26 15:26 UTC (permalink / raw)
To: Zuev Dmitry <envoy1@chat.ru>; pgsql-sql
On Tuesday 26 Nov 2002 1:14 pm, Zuev Dmitry wrote:
> Suppose you have a table T:
>
> A B
> 1 '111'
> 2 '222'
>
> How do you select A and B of the record with A maximum?
> 1) SELECT A, B FROM T ORDER BY A DESC LIMIT 1
>
> 2) SELECT A, B FROM T WHERE A IN (SELECT MAX(A) FROM T)
>
> What do yo think of the queries above? And give me the better
> implementation if possible.
If you have an index on A the first option will be faster. All PG has to do in
this case is check the end of the index and fetch one row.
--
Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Help on SQL query
@ 2019-02-15 17:22 Ian Tan <iantan1268@gmail.com>
2019-02-15 17:28 ` Re: Help on SQL query Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Ian Tan @ 2019-02-15 17:22 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org
Hello,
I am stuck on trying to write a SQL query (PostgreSQL 10.6) that I
cannot get my head around, and I cannot find anything after googling
for a while.
There are 2 tables:
CREATE TABLE car_parts (
id integer PRIMARY KEY,
name text
);
CREATE TABLE bill_of_materials (
parent_id integer REFERENCES car_parts (id),
child_id integer REFERENCES car_parts (id)
);
SELECT * FROM car_parts;
id | name
---------------------
01 | "Assembled 4WD"
02 | "Assembled 2WD"
03 | "V8 Engine"
04 | "V6 Engine"
05 | "Tow Bar"
SELECT * FROM bill_of_materials;
parent_id | child_id
--------------------
01 | 03
01 | 05
02 | 04
02 | 05
Question is,
How do I write an SQL query so that the "name" text in car_parts are
added to the bill_of_materials table, so that it looks like this:
parent_id | parent_name | child_id | child_name
------------------------------------------------------
01 | "Assembled 4WD" | 03 | "V8 Engine"
01 | "Assembled 4WD" | 05 | "Tow Bar"
02 | "Assembled 2WD" | 04 | "V6 Engine"
02 | "Assembled 2WD" | 05 | "Tow Bar"
Thank you.
Regards,
Ian
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help on SQL query
2019-02-15 17:22 Help on SQL query Ian Tan <iantan1268@gmail.com>
@ 2019-02-15 17:28 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-15 17:38 ` Re: Help on SQL query Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Andrew Gierth @ 2019-02-15 17:28 UTC (permalink / raw)
To: Ian Tan <iantan1268@gmail.com>; +Cc: pgsql-sql@lists.postgresql.org
>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes:
Ian> How do I write an SQL query so that the "name" text in car_parts
Ian> are added to the bill_of_materials table, so that it looks like
Ian> this:
The only trick with this is that you need to join the car_parts table
twice (once for parent and once for child), and to do that you need to
give it different alias names:
select bom.parent_id,
ppart.name as parent_name,
bom.child_id,
cpart.name as child_name
from bill_of_materials bom
join car_parts ppart on (ppart.id=bom.parent_id)
join car_parts cpart on (cpart.id=bom.child_id);
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help on SQL query
2019-02-15 17:22 Help on SQL query Ian Tan <iantan1268@gmail.com>
2019-02-15 17:28 ` Re: Help on SQL query Andrew Gierth <andrew@tao11.riddles.org.uk>
@ 2019-02-15 17:38 ` Rob Sargent <robjsargent@gmail.com>
2019-02-15 18:16 ` Re: Help on SQL query Ian Tan <iantan1268@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2019-02-15 17:38 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; +Cc: Ian Tan <iantan1268@gmail.com>; pgsql-sql@lists.postgresql.org
> On Feb 15, 2019, at 10:28 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>
>>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes:
>
> Ian> How do I write an SQL query so that the "name" text in car_parts
> Ian> are added to the bill_of_materials table, so that it looks like
> Ian> this:
>
> The only trick with this is that you need to join the car_parts table
> twice (once for parent and once for child), and to do that you need to
> give it different alias names:
>
> select bom.parent_id,
> ppart.name as parent_name,
> bom.child_id,
> cpart.name as child_name
> from bill_of_materials bom
> join car_parts ppart on (ppart.id=bom.parent_id)
> join car_parts cpart on (cpart.id=bom.child_id);
>
> --
> Andrew (irc:RhodiumToad)
>
Andrew, will you do my homework too?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help on SQL query
2019-02-15 17:22 Help on SQL query Ian Tan <iantan1268@gmail.com>
2019-02-15 17:28 ` Re: Help on SQL query Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-15 17:38 ` Re: Help on SQL query Rob Sargent <robjsargent@gmail.com>
@ 2019-02-15 18:16 ` Ian Tan <iantan1268@gmail.com>
2019-02-15 19:03 ` Re: Help on SQL query Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ian Tan @ 2019-02-15 18:16 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; pgsql-sql@lists.postgresql.org
Hello Andrew,
Thank you, I appreciate your response and your help.
Hello Rob,
I learn in my own time and had no one to ask. If pgsql-sql is not the
correct forum for these kinds of question, kindly let me know.
Thank you.
Regards,
Ian
On Fri, 15 Feb 2019 at 17:38, Rob Sargent <robjsargent@gmail.com> wrote:
>
>
>
> > On Feb 15, 2019, at 10:28 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
> >
> >>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes:
> >
> > Ian> How do I write an SQL query so that the "name" text in car_parts
> > Ian> are added to the bill_of_materials table, so that it looks like
> > Ian> this:
> >
> > The only trick with this is that you need to join the car_parts table
> > twice (once for parent and once for child), and to do that you need to
> > give it different alias names:
> >
> > select bom.parent_id,
> > ppart.name as parent_name,
> > bom.child_id,
> > cpart.name as child_name
> > from bill_of_materials bom
> > join car_parts ppart on (ppart.id=bom.parent_id)
> > join car_parts cpart on (cpart.id=bom.child_id);
> >
> > --
> > Andrew (irc:RhodiumToad)
> >
>
> Andrew, will you do my homework too?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Help on SQL query
2019-02-15 17:22 Help on SQL query Ian Tan <iantan1268@gmail.com>
2019-02-15 17:28 ` Re: Help on SQL query Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-15 17:38 ` Re: Help on SQL query Rob Sargent <robjsargent@gmail.com>
2019-02-15 18:16 ` Re: Help on SQL query Ian Tan <iantan1268@gmail.com>
@ 2019-02-15 19:03 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2019-02-15 19:03 UTC (permalink / raw)
To: Ian Tan <iantan1268@gmail.com>; +Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; pgsql-sql@lists.postgresql.org
On 2/15/19 11:16 AM, Ian Tan wrote:
> Hello Andrew,
> Thank you, I appreciate your response and your help.
>
> Hello Rob,
> I learn in my own time and had no one to ask. If pgsql-sql is not the
> correct forum for these kinds of question, kindly let me know.
>
> Thank you.
>
> Regards,
> Ian
>
> On Fri, 15 Feb 2019 at 17:38, Rob Sargent <robjsargent@gmail.com> wrote:
>>
>>
>>> On Feb 15, 2019, at 10:28 AM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>
>>>>>>>> "Ian" == Ian Tan <iantan1268@gmail.com> writes:
>>> Ian> How do I write an SQL query so that the "name" text in car_parts
>>> Ian> are added to the bill_of_materials table, so that it looks like
>>> Ian> this:
>>>
>>> The only trick with this is that you need to join the car_parts table
>>> twice (once for parent and once for child), and to do that you need to
>>> give it different alias names:
>>>
>>> select bom.parent_id,
>>> ppart.name as parent_name,
>>> bom.child_id,
>>> cpart.name as child_name
>>> from bill_of_materials bom
>>> join car_parts ppart on (ppart.id=bom.parent_id)
>>> join car_parts cpart on (cpart.id=bom.child_id);
>>>
>>> --
>>> Andrew (irc:RhodiumToad)
>>>
>> Andrew, will you do my homework too?
My apologies. It looked suspiciously like a homework question to me.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2019-02-15 19:03 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-26 13:14 help on sql query Zuev Dmitry <envoy1@chat.ru>
2002-11-26 15:26 ` Richard Huxton <dev@archonet.com>
2019-02-15 17:22 Help on SQL query Ian Tan <iantan1268@gmail.com>
2019-02-15 17:28 ` Re: Help on SQL query Andrew Gierth <andrew@tao11.riddles.org.uk>
2019-02-15 17:38 ` Re: Help on SQL query Rob Sargent <robjsargent@gmail.com>
2019-02-15 18:16 ` Re: Help on SQL query Ian Tan <iantan1268@gmail.com>
2019-02-15 19:03 ` Re: Help on SQL query Rob Sargent <robjsargent@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox