|
View previous topic :: View next topic
|
| Author |
Message |
mubs55
New User
Joined: 19 Apr 2006
Posts: 12
|
|
|
|
Hi,
I have got to select records from a table based on col B below it should have either 'AC' or 'TM' but if both 'AC' and 'TM' are present for same value in 'A' then it should select only the first record.
table
| Code: |
A B
1 AC
1 TM
1 CA
2 AC
2 TM
3 AC
4 TM |
output:
| Code: |
A B
1 AC
2 AC
3 AC
4 TM |
let me know how to write the query please.
Regards,
Mubeena |
|
| Back to top |
|
 |
raghunathns
Active User
.jpg)
Joined: 08 Dec 2005
Posts: 127
Location: rochester
|
|
|
|
try like this.
| Code: |
select * from tbl1 x
where 0 = (select count(*) from tbl1 y
where y.a = x.a and y.b < x.b)
order by x.a, x.b |
.. |
|
| Back to top |
|
|
raghunathns
Active User
Joined: 08 Dec 2005
Posts: 127
Location: rochester
|
|
|
|
you can try this way also.
| Code: |
select * from tbl1 x
where x.b = (select min(b) from tbl1 y where y.a = x.a)
order by x.a, x.b |
..
change min to max to get maximum b value rows.
. |
|
| Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005
Posts: 1448
Location: Chicago, IL
|
|
|
|
Mubeena... Did you get answer from above queries, of what you were looking for ?
Check it what I think on this...
| Code: |
| SELECT * FROM (SELECT DISTINCT A, B FROM TABLE) WHERE B IN ('AC','TM'); |
Inner query should return the records with only first row among duplicates, outer query would filter out to keep only the one having "AC" or "TM".
I could not test it before posting, but a little variation of this concept should provide you the result- in case this doesnt work. |
|
| Back to top |
|
|
|
|
