Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    80

    Unanswered: partial distinct

    Hi All,

    I am a novice here. so need some help in getting this query written.

    i have tables like this

    add
    -----------
    pers_id
    add_id
    ext_add_id
    st_name
    st_no

    I have to fetch all the columns from add but the rows needs to be distinct on the first 3 columns only. please can you help me with this.

    I have tried distinct which will create unique on all the columns.

    Thanks for your help in adavance!!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have tried distinct which will create unique on all the columns.
    SELECT DISTINCT PERS_ID, ADD_ID, EXT_ADD_ID FROM ADD -- as part of a sub-select
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2005
    Posts
    80
    thanks for the reply. but couldn't understand your solution. it doesnt matter which row is picked up for st_name and st_no. hence could you please be more verbose about it?

    Thanks again!!

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    INSERT INTO ADD VALUES(1,1,1,1,1);
    INSERT INTO ADD VALUES(1,1,1,1,2);
    INSERT INTO ADD VALUES(1,1,1,1,3);
    INSERT INTO ADD VALUES(2,1,1,1,1);
    INSERT INTO ADD VALUES(2,1,1,1,2);
    INSERT INTO ADD VALUES(2,1,1,1,3);
    INSERT INTO ADD VALUES(3,1,1,1,1);
    INSERT INTO ADD VALUES(3,1,1,1,2);
    INSERT INTO ADD VALUES(3,1,1,1,3);

    which rows from above should be returned & WHY these rows & not any others?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2005
    Posts
    80
    INSERT INTO ADD VALUES(1,1,1,1,1);
    INSERT INTO ADD VALUES(2,1,1,1,2);
    INSERT INTO ADD VALUES(3,1,1,1,3);

    any random values for the last column is alright like above. As I am looking for distinct only for the first 3 columns

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  SELECT ID1, ID2, ID3, min(id4), min(id5) FROM ADD1
      2* GROUP BY id1, id2, id3
    18:45:37 SQL> /
    
           ID1	  ID2	     ID3   MIN(ID4)   MIN(ID5)
    ---------- ---------- ---------- ---------- ----------
    	 2	    1	       1	  1	     1
    	 1	    1	       1	  1	     1
    	 3	    1	       1	  1	     1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jan 2005
    Posts
    80
    It did the trick. didnt knew that we can use min max on the characters fields.

    Thank you very much for the help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •