February 9, 2006 at 3:34 pm
Could you please help me out with a SQL statement?
TABLE_A
name_col.....rev_no.....order_no.....key_col
ABC.............0............123............
ABC.............1............123............
ABC.............2............123............XYZ
DEF.............0............456............RRR
GHI.............0............999............
GHI.............1............999............XYZ
This is what my result set should look like:
order_no
123
999
I want the distinct order_no's for a particular key_col where I am am passing in the key_col as a value. I also only want the order_no that is associated with the max(rev_no) for the particular name_col.
February 9, 2006 at 4:15 pm
First one:
-------------------------------------------
Select DISTINCT order_no from Table_A
Where key_col = 'XYZ'
-------------------------------------------
Second One
-----------------------------------------
Select order_no from Table_A where rev_no IN
(Select MAX(rev_no) from Table_A where name_col = 'XYZ'
group by name_col)
----------------------------------------
You may move Where outside the subquery, in this case the subquery will return a list of the max (rev_no) for each name_col.
Regards,Yelena Varsha
February 9, 2006 at 9:41 pm
Thank you but this does not work, it will only bring back the name_col with the max rev_no - 'ABC'.
I want all name_col's for the key_col ('XYZ') and their max rev_no for each.
February 9, 2006 at 11:01 pm
your sample data is not sufficient to represent what you want. Post more sample data and the exepected result
February 9, 2006 at 11:44 pm
HI ,
USE THIS,
SELECT NAME_COL,
(SELECT MAX(REV_NO) FROM TABLE_A WHERE B.NAME_COL=NAME_COL AND B.KEY_COL=KEY_COL)
ORDER_NO,
KEY_COL
FROM TABLE_A B
GROUP BY NAME_COL,
ORDER_NO,
KEY_COL
February 10, 2006 at 8:25 am
AMIT -
That got me what I needed - thank you so much!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply