[SQL] Ambiguous error message

2004-01-02 Thread Samuel Tardieu 
In PostgreSQL 7.4, the following select:

select texten, total
from (select protocolid, count(*) as total) from ips where catid=1
  group by protocolid order by protocolid) as c
  inner join protocols using (protocolid);

gives the error message:

ERROR:  subquery in FROM must have an alias
HINT:  For example, FROM (SELECT ...) [AS] foo.

Why isn't the "as c" considered as an alias?

  Sam
-- 
Samuel Tardieu -- [EMAIL PROTECTED] -- http://www.rfc1149.net/sam


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Ambiguous error message

2004-01-02 Thread Michael Glaesemann 
Hi Sam,

I'm not quite sure what you want the query to return, but you've got a 
problem with your parentheses. You've got two FROM clauses and an INNER 
JOIN, which together aren't arranged properly. I've rearranged your 
query a little, but I haven't changed anything. Perhaps this'll make it 
a little clearer to you.

On Jan 2, 2004, at 10:49 AM, Samuel Tardieu wrote:

In PostgreSQL 7.4, the following select:

select texten, total
from (select protocolid, count(*) as total)
from ips
where catid=1
group by protocolid order by protocolid) as c
inner join protocols using (protocolid);
 If you explain a little more what your query is trying to return, I 
might be able to help more.

Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Ambiguous error message

2004-01-02 Thread Tom Lane 
Samuel Tardieu <[EMAIL PROTECTED]> writes:
> In PostgreSQL 7.4, the following select:
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
>   group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);

> gives the error message:

> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.

> Why isn't the "as c" considered as an alias?

It's complaining about this part:

from (select protocolid, count(*) as total) from ips where
 ^^

It hasn't gotten as far as noticing your mismatched parentheses and
two top-level FROM keywords yet ;-).

It would probably be helpful if this message included a cursor location...

regards, tom lane

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Ambiguous error message

2004-01-02 Thread Samuel Tardieu 
On  2/01, Michael Glaesemann wrote:

| I'm not quite sure what you want the query to return, but you've got a 
| problem with your parentheses.

I can't believe this :) I got hit by the "inner query needs to be aliased"
message and added it to the wrong place and them munged the query a lot.

The correct one was of course:
# select texten, total from (select protocolid, count(*) as total from ips
  where catid=1 group by protocolid order by protocolid) as c
  inner join protocols using (protocolid);

(PostgreSQL is used for the will-spam-for-food black-list, see
 http://www.rfc1149.net/wsff)

   Sam


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] Ambiguous error message

2004-01-02 Thread Samuel Tardieu 
On  3/01, Samuel Tardieu wrote:

| On  2/01, Michael Glaesemann wrote:
| 
| | I'm not quite sure what you want the query to return, but you've got a 
| | problem with your parentheses.
| 
| I can't believe this :) I got hit by the "inner query needs to be aliased"
| message and added it to the wrong place and them munged the query a lot.
| 
| The correct one was of course:
| # select texten, total from (select protocolid, count(*) as total from ips
|   where catid=1 group by protocolid order by protocolid) as c
|   inner join protocols using (protocolid);

(with the "order by" part moved at the end of the query to avoid losing the
order during the inner join)

  Sam


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Ambiguous error message

2004-01-02 Thread Michael Fuhr 
On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> In PostgreSQL 7.4, the following select:
> 
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
>   group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);
> 
> gives the error message:
> 
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
> 
> Why isn't the "as c" considered as an alias?

The query has a right parenthesis after "as total" that looks like
it shouldn't be there.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[SQL] sort by on two columns

2004-01-02 Thread Andy Lewis 
Title: Message



Hi 
All,
Is it possible to 
sort by two columns? Using the query below?
 
SELECT table1.name, 
table2.name,  FROM table1, table2 WHERE 
table1.id = table2.id ORDER BY 
 
I want to be able to 
sort the names select from two different tables and two different colums(same 
data type).
 
Is this 
possible?
 
Thanks,
 
Andy

Re: [SQL] Ambiguous error message

2004-01-02 Thread Bertrand Petit 
On Fri, Jan 02, 2004 at 05:49:46PM +0100, Samuel Tardieu wrote:
> 
> select texten, total
> from (select protocolid, count(*) as total) from ips where catid=1
^
+--- There
>   group by protocolid order by protocolid) as c
>   inner join protocols using (protocolid);
> 
> gives the error message:
> 
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
> 
> Why isn't the "as c" considered as an alias?

That's maybe due to the highlighted spurious parenthesis.

-- 
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] sort by on two columns

2004-01-02 Thread Andy Lewis 
Hi Michael,

Yes, I understand this but, I would like to have the results of both
"table1.name, table2.name"
sorted as one column.

Is this possible?

Thanks,

Andy

-Original Message-
From: Michael Glaesemann [mailto:[EMAIL PROTECTED] 
Sent: Friday, January 02, 2004 8:40 PM
To: Andy Lewis
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] sort by on two columns


Hi Andy,

On Jan 2, 2004, at 7:15 PM, Andy Lewis wrote:

> Is it possible to sort by two columns? Using the query below?
>
> SELECT table1.name, table2.name,  FROM table1,

> table2 WHERE table1.id = table2.id ORDER BY 
>
> I want to be able to sort the names select from two different tables
> and
> two different colums(same data type).


If you want to order by table1.name and table2.name, just enter them in 
a comma-separated-list after ORDER BY, e.g.,

SELECT table1.name, table2.name, 
FROM table1, table2
WHERE table1.id = table2.id
ORDER BY table1.name, table2.name

Check out the following link for the online docs:


It doesn't explicitly give you an example of sorting on more than one 
column, but the syntax explanation at the top includes it.

Does this help?

Michael Glaesemann
grzm myrealbox com


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] sort by on two columns

2004-01-02 Thread Michael Glaesemann 
On Jan 2, 2004, at 8:55 PM, Andy Lewis wrote:
Yes, I understand this but, I would like to have the results of both
"table1.name, table2.name"
sorted as one column.
Is this possible?
So you want one column of name, including names from both table1 and 
table2? In that case, you need to use UNION, I believe. If it's 
something else, I'm not sure I understand what you're asking. Perhaps 
you could include a sample of what you're getting, along with what 
you'd like to see?

Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] sort by on two columns

2004-01-02 Thread Adam Ruth 
Sounds like you may want to concatenate the columns:

... order by table1.name || table2.name

The sorting would then be performed on both of the them as though they 
were one column.

Adam Ruth

On Jan 2, 2004, at 8:04 PM, Michael Glaesemann wrote:

On Jan 2, 2004, at 8:55 PM, Andy Lewis wrote:
Yes, I understand this but, I would like to have the results of both
"table1.name, table2.name"
sorted as one column.
Is this possible?
So you want one column of name, including names from both table1 and 
table2? In that case, you need to use UNION, I believe. If it's 
something else, I'm not sure I understand what you're asking. Perhaps 
you could include a sample of what you're getting, along with what 
you'd like to see?

Michael Glaesemann
grzm myrealbox com
---(end of 
broadcast)---
TIP 7: don't forget to increase your free space map settings



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] sort by on two columns

2004-01-02 Thread Chris Travers 
Title: Message



SELECT * FROM customers ORDER BY last_name, 
first_name
Works for me.

  - Original Message - 
  From: 
  Andy Lewis 
  
  To: [EMAIL PROTECTED] 
  Sent: Saturday, January 03, 2004 8:15 
  AM
  Subject: [SQL] sort by on two 
  columns
  
  Hi 
  All,
  Is it possible to 
  sort by two columns? Using the query below?
   
  SELECT 
  table1.name, table2.name,  FROM table1, 
  table2 WHERE table1.id = table2.id ORDER BY 
  
   
  I want to be able 
  to sort the names select from two different tables and two different 
  colums(same data type).
   
  Is this 
  possible?
   
  Thanks,
   
  Andy

Re: [SQL] sort by on two columns

2004-01-02 Thread Michael Glaesemann 
Hi Andy,

On Jan 2, 2004, at 7:15 PM, Andy Lewis wrote:

Is it possible to sort by two columns? Using the query below?

SELECT table1.name, table2.name,  FROM table1,
table2 WHERE table1.id = table2.id ORDER BY 
I want to be able to sort the names select from two different tables 
and
two different colums(same data type).


If you want to order by table1.name and table2.name, just enter them in 
a comma-separated-list after ORDER BY, e.g.,

SELECT table1.name, table2.name, 
FROM table1, table2
WHERE table1.id = table2.id
ORDER BY table1.name, table2.name
Check out the following link for the online docs:

It doesn't explicitly give you an example of sorting on more than one 
column, but the syntax explanation at the top includes it.

Does this help?

Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings