Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: select certain records from table

    Hi Guys,

    Just need some hints, im aware of analytic functions where we can select records based on partition clause.

    Basically if i have the following set of records

    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    1, abc, E, 2-jan-08
    1, abc, K, 3-jan-08
    2, abc, E, 2-jan-08
    2, abc, K, 3-jan-08

    How do i actually extract those records which have col3 = A (if exists), otherwise just extract any first record order by col4, partition by col1
    Meaning the output will be


    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    2, abc, E, 2-jan-08

    i plan to build 2 queries with union all clause,

    1) with all records which has col3 = A
    2) with all records which has col3 <> A and here i will have a nested query to select first value , partition order by col4.

    But i end up having duplicate col1 in a situation where both queries (1) & (2) returns records for a particular data in col1, so intead of getting this

    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    2, abc, E, 2-jan-08


    I might get..


    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    1, abc, E, 2-jan-08
    2, abc, E, 2-jan-08

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You need to realize that rows in a table have NO inherent order.
    Rows in a table are like balls in basket.
    How do you identify the first green ball in a basket?
    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
    Nov 2008
    Posts
    26
    Quote Originally Posted by anacedent
    You need to realize that rows in a table have NO inherent order.
    Rows in a table are like balls in basket.
    How do you identify the first green ball in a basket?

    Yeah, i understand your point. But im identifying rows based on condition

    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    1, abc, E, 2-jan-08
    1, abc, K, 3-jan-08
    2, abc, E, 2-jan-08
    2, abc, K, 3-jan-08

    Basically the unique record is identified by col1 & col2.

    So i just need to find a way, for instance to extract all rows which have col2 = 'A', if theres no rows for a particular col1 which has col2 = 'A', then i will take the first row which has the latest col4..

  4. #4
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    1, abc, E, 2-jan-08
    1, abc, K, 3-jan-08
    2, abc, E, 2-jan-08
    2, abc, K, 3-jan-08

    1) with all records which has col3 = A
    2) with all records which has col3 <> A and here i will have a nested query to select first value , partition order by col4.

    from my understanding the following sql can do what you need

    SELECT col1,col2,col3,col4
    FROM (SELECT col1,col2,col3,col4 FROM t WHERE col3 = 'A'
    UNION
    SELECT max(col1) col1 ,col2, col3 ,col4 FROM t WHERE col3 != 'A'
    GROUP BY col4) ORDER BY col3;
    Last edited by hasan_uiu; 02-09-09 at 23:51.
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  5. #5
    Join Date
    Nov 2008
    Posts
    26
    Quote Originally Posted by hasan_uiu
    col1 | col2 | col3 | col4
    -------------------
    1, abc, A, 1-jan-08
    1, abc, E, 2-jan-08
    1, abc, K, 3-jan-08
    2, abc, E, 2-jan-08
    2, abc, K, 3-jan-08

    1) with all records which has col3 = A
    2) with all records which has col3 <> A and here i will have a nested query to select first value , partition order by col4.

    from my understanding the following sql can do what you need

    SELECT col1,col2,col3,col4
    FROM (SELECT col1,col2,col3,col4 FROM t WHERE col3 = 'A'
    UNION
    SELECT max(col1) col1 ,col2, col3 ,col4 FROM t WHERE col3 != 'A'
    GROUP BY col4) ORDER BY col3;

    hasan_uiu, appreciate your help, but your query does not seem right

Posting Permissions

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