help in writing sql select statement

[apar_6]

Hi,

U could probably try this qry below.

select res.*,rownum
from
(
SELECT DISTINCT sss.user_name, 1,
(SELECT COUNT (user_name)
FROM saved_searches
WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1)
--[over here I need sequential nos 1,2,3 for each row]
FROM saved_searches sss
ORDER BY (SELECT COUNT (user_name)
FROM saved_searches
WHERE sss.user_name = 'Phil Watson' AND ROWNUM =1) DESC,
sss.user_name
) res

Output of this would be (assuming 3 users TEST1, TEST2, TEST3) :

Username 1 Cnt Rownum
TEST1 1 1 1
TEST2 1 0 2
TEST3 1 0 3

Reg,
Aparna