-
Selecting Only the First Row in a Query
Say a number of people have 2 or 3 addresses each in a query result.
How do I select only the first address displayed for each person.
For example:
John Taylor 33 Dundas Ave.
John Taylor 55 Shane Ave.
John Taylor 786 Edward St.
Ted Charles 785 Bloor St.
Ted Charles 90 New York Ave.
All I want is the first address that is displayed for each person
which is:
John Taylor 33 Dundas Ave.
Ted Charles 785 Bloor St.
Wondering if this is possible in Access?
Marcus
******
-
unless there is an explicit order on your select statement you can not select for row #1 of a result set as you can not rely upon oracle to return a result set in any particular order.
it looks like for your issue here, if your address is stored seperately, you could use a group by cluase on name and a MIN function on the address.
-
As jkoopmann said:
Let's assume that the structure is like this:
create table people(name varchar2(50), addr varchar2(100));
insert into people values('John Taylor','33 Dundas Ave');
insert into people values('John Taylor','55 Shane Ave');
insert into people values('John Taylor','786 Edward St');
insert into people values('Ted Charles','785 Bloor St');
insert into people values('Ted Charles','90 New York Ave');
So, using ORACLE, this should be a sulution:
create sequence people_seq;
create table temp_people as
select people_seq.nextval,name,addr from people;
Finally, the Query is:
select name, addr from temp_people
where nextval in (select min(nextval) from temp_people
group by name);
RESULT :
name addr
John Taylor 33 Dundas Ave
Ted Charles 785 Bloor St
Good luck!!
-
thanks for the test data.
will also just work like this:
25-MAY-05 : JKOOPMANN@k10gutf8 > create table people(name varchar2(50), addr varchar2(100));
Table created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('John Taylor','33 Dundas Ave');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('John Taylor','55 Shane Ave');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('John Taylor','786 Edward St');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('Ted Charles','785 Bloor St');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('Ted Charles','90 New York Ave');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > select name,min(addr) addr from people group by name;
NAME ADDR
------------------------------ ------------------------------
John Taylor 33 Dundas Ave
Ted Charles 785 Bloor St
-
But it won't work with:
create table people(name varchar2(50), addr varchar2(100));
insert into people values('John Taylor','33 Dundas Ave');
insert into people values('John Taylor','55 Shane Ave');
insert into people values('John Taylor','786 Edward St');
insert into people values('Ted Charles','9985 other St');
insert into people values('Ted Charles','90 New York Ave');
Let me know if I m wong ..
-
that is why i had stated in the first post that there is no "order" in the way oracle will return the rows to you. you can not expect to retrieve rows in the same order you insert them. therefore you need to impose a sort order on them. the group by and MIN function basically imposes a binary sort and takes the "lowest" address as the first row of a particular name.
since 90 is before 9985 it will return the address with the 90 as the first row.
So in essence it does work.
25-MAY-05 : JKOOPMANN@k10gutf8 > create table people(name varchar2(50), addr varchar2(100));
Table created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('John Taylor','33 Dundas Ave');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('John Taylor','55 Shane Ave');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('John Taylor','786 Edward St');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('Ted Charles','9985 other St');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > insert into people values('Ted Charles','90 New York Ave');
1 row created.
25-MAY-05 : JKOOPMANN@k10gutf8 > select name,min(addr) addr from people group by name;
NAME ADDR
---------------------------------------- ----------------------------------------
John Taylor 33 Dundas Ave
Ted Charles 90 New York Ave
-
Many thanks for the solutions.
It doesn't matter to me what row is selected, whether the first row or not. So to that degree your solution will work.
In the example,
create sequence people_seq;
create table temp_people as
select people_seq.nextval,name,addr from people;
Finally, the Query is:
select name, addr from temp_people
where nextval in (select min(nextval) from temp_people
group by name);
is nextval similar to an auto increment number?
Marcus
******
Last edited by phobos123; 05-26-2005 at 01:30 AM.
-
you really don't need to create a sequence and temporary table to satisfy this.
just use the SQL :
select name,min(addr) addr from people group by name;
-
You are right, it is a little bit confused.
In this case: create table temp_people as
select people_seq.nextval,name,addr from people;
"nextval" is (let's say) a method that generates the sequence's next value.
For more info about Oracle Sequence: go to http://www.techonthenet.com/oracle/sequences.htm
but:
Because of the way the table temp_people is created (with a query) its description is:
temp_people(
NEXTVAL NUMBER
NAME VARCHAR2(50)
ADDR VARCHAR2(100)
)
and you can see "nextval" is regular field of the table "temp_people".
Anyway, if it doesn't matter to you what the row is selected, just go to the jkoopmann's solution:
select name,min(addr) addr from people group by name;
-
A more extensible approach would be to use some of analytic functions in oracle. Very powerful but proprietary to oracle. Not SQL standard.
Example:
--lets say you have a table with 3 fields, as such
create table people(name varchar2(20), addr varchar2(20), postcode varchar2 (5));
insert into people values('John Taylor','33 Dundas Ave', '22222');
insert into people values('John Taylor','55 Shane Ave', '11111');
insert into people values('John Taylor','786 Edward St', '33333');
insert into people values('Ted Charles','9985 other St', '44444');
insert into people values('Ted Charles','90 New York Ave', '66666');
SELECT
name,
MAX(addr) KEEP (DENSE_RANK FIRST ORDER BY addr) as addr,
MAX(postcode) KEEP (DENSE_RANK FIRST ORDER BY addr) as postcode
FROM people
GROUP BY name
NAME ADDR POSTCODE
-------------------- -------------------- --------
John Taylor 33 Dundas Ave 22222
Ted Charles 90 New York Ave 66666
2 rows selected
Last edited by jkoopmann; 05-27-2005 at 11:07 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|