help in writing sql select statement

[LKBrwn_DBA]

You could try something like this:

SELECT * FROM (
SELECT DISTINCT sss.user_name, 1,
(SELECT COUNT (user_name)
FROM saved_searches
WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1),
RANK() OVER (ORDER BY sss.user_name, rowid) SEQ
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)
;