June 27, 2012 at 9:21 am
how do i select the second row only from my table?
my table name is #ProxyLog_count_2 and the column name i want is ClientUserName
What would it look like?
June 27, 2012 at 9:22 am
What is the definition of that table?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 27, 2012 at 9:23 am
And what are you ordering by to determine the second row?
June 27, 2012 at 9:25 am
achen9291 (6/27/2012)
how do i select the second row only from my table?my table name is #ProxyLog_count_2 and the column name i want is ClientUserName
What would it look like?
There is no such thing as "the second row of a table." Only the second row of an ordered set.
Jared
CE - Microsoft
June 27, 2012 at 9:31 am
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2) Group by ClientUserName, DestHost order by counts desc
I have to replace the bolded part with something that will get the 2nd row from the table. I guess it's order by counts? Sorry I'm new to sql.
June 27, 2012 at 9:39 am
achen9291 (6/27/2012)
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_recordwhere ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2) Group by ClientUserName, DestHost order by counts desc
I have to replace the bolded part with something that will get the 2nd row from the table. I guess it's order by counts? Sorry I'm new to sql.
What are you ordering #ProxyLog_count_2 by to determine the 2nd row?
June 27, 2012 at 9:40 am
achen9291 (6/27/2012)
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_recordwhere ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2) Group by ClientUserName, DestHost order by counts desc
I have to replace the bolded part with something that will get the 2nd row from the table. I guess it's order by counts? Sorry I'm new to sql.
Look at this and see if it helps you:
SELECT ROW_NUMBER() OVER(GROUP BY ClientUserName, DestHost ORDER BY counts DESC), ClientUserName, DestHost, counts
FROM #proxyLog
GROUP BY ClientUserName, DestHost
I cannot test this as you have not provided DDL or sample data. Please look at the link in my signatire about how to post for the best response.
Jared
CE - Microsoft
June 27, 2012 at 9:50 am
Oh, it would be ordered by counts desc
June 27, 2012 at 10:04 am
Like Jared said above, I can't test this without any sample data, but give this a try and see if it works. If not you can try to play with it to fit your needs or post sample data per the link in Jared's signature to get better help.
;with cte as
(
Select ClientUserName, DestHost, ROW_NUMBER() over (order by count(DestHost) desc) rn
from #ProxyLog_count_2
Group by ClientUserName, DestHost
)
select plr.ClientUserName, plr.DestHost, COUNT(plr.DestHost) counts
from cte c
join #ProxyLog_record plr on plr.ClientUserName = c.ClientUserName and plr.DestHost = c.DestHost
where c.rn = 2
Edit: You won't want a partition by in your over statement on the ROW_NUMBER().
June 27, 2012 at 10:57 am
Sorry I hope I can clarify better.
#ProxyLog_count_2 is ordered by counts.
there are 2 columns in proxylog_count_2, ClientUserName and counts. but i only want to display ClientUserName
so i use this.
Select top 2 ClientUserName from #ProxyLog_count_2
then it will show 2 rows from ClientUserName. I want the 2nd data only to show. I'm not sure how to use RowNum.
so when i get the code, i need to replace it into here, where the bolded part is.
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2) Group by ClientUserName, DestHost order by counts desc
does that help?
June 27, 2012 at 11:16 am
achen9291 (6/27/2012)
Sorry I hope I can clarify better.#ProxyLog_count_2 is ordered by counts.
there are 2 columns in proxylog_count_2, ClientUserName and counts. but i only want to display ClientUserName
so i use this.
Select top 2 ClientUserName from #ProxyLog_count_2
then it will show 2 rows from ClientUserName. I want the 2nd data only to show. I'm not sure how to use RowNum.
so when i get the code, i need to replace it into here, where the bolded part is.
Select ClientUserName, DestHost, count(DestHost) counts from #ProxyLog_record
where ClientUserName =(Select top 1 ClientUserName from #ProxyLog_count_2) Group by ClientUserName, DestHost order by counts desc
does that help?
Try this
;WITH CTE AS
(SELECT ClientUserName,Counts, Row_number() Over(order by counts DESC) AS RN
FROM #ProxyLog_count_2)
SELECT ClientUserName FROM CTE WHERE RN = 2
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
June 27, 2012 at 11:25 pm
Hope this will help you
SElect top 1 ClientUserName from (SELECT top 2 row_number() over(order by counts DESC) as ID
FROM #ProxyLog_count_2
GROUP BY ClientUserName)"metval"
where metval.ID=2
June 28, 2012 at 4:01 am
select * from (
select *,ROW_NUMBER()over (order by mark desc)rn from tablename) a where rn=3
June 28, 2012 at 4:02 am
Please loook into the below query
select * from (
select *,ROW_NUMBER()over (order by mark desc)rn from mark ) a where rn=3
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply