|
I don't know if this will work, but why not try it
? :)
select distinct fullname, distinct staff_id, loginname from staff
where
staff_id in
(select staff_id from staffmembership where
staffgroup_id=#SomeIdValue#)
order by fullname, staff_id
Next one is more complicate but with more chances
to succeed :)
select staff_id, fullname, loginname from
(
select distinct on (staff_id) staff_id, fullname, loginname from
staff
where staff_id in
(select staff_id from staffmembership
where staffgroup_id=#SomeIdValue#)
order by staff_id
)
order by fullname
Let me know
:)
----- Original Message -----
Sent: Wednesday, April 28, 2004
12:27
Subject: [SQL] select distinct and order
by
Hello everybody,
I have a short question:
I'm
trying to run the following query:
select distinct on (staff_id) staff_id,
fullname, loginname from staff
where staff_id in
(select
staff_id from staffmembership where staffgroup_id=#SomeIdValue#)
order by
fullname
I get the follwing error:
select distinct on expressions
must match the initial order by _expression_
Does this mean that I can
only order by the same fields as the ones that I use in the distinct?
If
so, is there still a way that I can select distinct on the keyfield, and still
sort by the name. However unlikely, it could happen that two people have the
same name, so a distinct on fullname could make problems.
The
facts:
- PostgreSQL 7.3.2 running on RH 9
- pgODBC 7.3.0200
- Borland
Delphi 7 enterprise
Regards,
Stijn Vanroye
-=[Today I got
more responsabilities from my boss, as from now I'm responsible for everything
that goes wrong ...]=-
---------------------------(end of
broadcast)---------------------------
TIP 7: don't forget to increase your
free space map settings
|
