Results 1 to 10 of 10

Thread: Selecting Only the First Row in a Query

  1. #1
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    4

    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
    ******

  2. #2
    Join Date
    Mar 2003
    Posts
    468
    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.

  3. #3
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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!!

  4. #4
    Join Date
    Mar 2003
    Posts
    468
    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

  5. #5
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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 ..

  6. #6
    Join Date
    Mar 2003
    Posts
    468
    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

  7. #7
    Join Date
    Aug 2004
    Location
    Canada
    Posts
    4
    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.

  8. #8
    Join Date
    Mar 2003
    Posts
    468
    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;

  9. #9
    Join Date
    Apr 2005
    Location
    florida
    Posts
    89
    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;

  10. #10
    Join Date
    May 2005
    Posts
    2
    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
  •