# Thread: select certain records from table

1. Registered User
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. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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?

3. Registered User
Join Date
Nov 2008
Posts
26
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. Registered User
Join Date
Jan 2009
Location
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.

5. Registered User
Join Date
Nov 2008
Posts
26
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;