help in writing sql select statement

[thomasp]

Hi,

Can someone please help me write this sql statement?

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;

This query shouls give the result as follows:
USER_NAME|1|SELECT COUNT (user_name)|Column that I want to add
TEST1 1 1 1
TEST2 1 0 2
TEST3 1 0 3


Thanks a lot for your help. I just need to know how to add the last column .

[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)
;

[thomasp]

Hi and thanks for the reply.

I'm not getting the resultset I need. It's looping around the same thing 3 times.

[LKBrwn_DBA]

Maybe you have to re-think the statement because
(SELECT COUNT (user_name)
FROM saved_searches
WHERE sss.user_name = 'TESTADM' AND ROWNUM = 1)
will allways produce either 0 or 1!!!

[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