You can assign an alias to a table in a query. It can be done for readability and has to be done if you want to use the same table more than once in the query.

SELECT * FROM ccs c1 ;
               ^   ^
               |   table alias            
               table name

>Hi Sergey, thanks for your reply.
>
>I am not sure where you are getting the C1 and C2 from in the examples .
>I just have one input file with the data
>(My usual sysntax is Select * from Input file into Cursor Myoutputfile)
>
>Regards,
>
>Gerard
>
>
>>

>>SELECT * FROM ccs c1;
>>	WHERE NOT EXISTS (SELECT * FROM ccs  c2  ;
>>	 	WHERE c2.Country = c1.country ;
>>	 		AND c2.Sales > c1.sales)
>>* or
>>SELECT * FROM ccs c1;
>>	WHERE Sales = (SELECT MAX(Sales) FROM ccs c2  ;
>>	 	WHERE c2.Country = c1.country )
>>* or
>>SELECT ccs.* FROM ccs;
>>	JOIN (SELECT Country, MAX(Sales) AS MaxSales FROM ccs GROUP BY 1) ms ;
>>	 	ON ms.Country = ccs.country ;
>>	 		AND ms.MaxSales = ccs.sales
>>
>>
>>

>>>I have a table similar to following:
>>

>>>Country    City     Sales
>>>USA        NY       100
>>>USA        SF       500
>>>USA        BO       200
>>>UK         LO       300
>>>UK         BE       700
>>>UK         MA       400
>>

>>>and I want to select out the top city in each country based on sales
>>>so the result-set from the above should be:
>>

>>>USA       SF     500
>>>UK        BE     700
>>

>>>Can this be done using one SQL statement ?