CODEWITHSUNDEEP
sqlt-sql
Cloud
Cloud

Reputation: 213

Selecting the 2nd row in sql

I want to select the second row only from the table. From the ClientUserName column.

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

The (Select top 1 ClientUserName from #ProxyLog_count_2) shows top 1 only but I need to get the 2nd data from that table. How can I do this?

Upvotes: 0

Views: 16577

Answers (6)

mhead
mhead

Reputation: 21

SELECT Top 1 a.ClientUserName, a.DestHost, a.counts
FROM
(
SELECT Top 2
    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
)
as a
ORDER BY a.Counts ASC

Upvotes: 0

jaypeagi
jaypeagi

Reputation: 3141

Easiest way would be to use the ROW_NUMBER() method like so:

WITH c AS (
SELECT
    ClientUserName, DestHost, count(DestHost) counts, ROW_NUMBER() OVER(ORDER BY count(DestHost)) AS rowNum
FROM 
    #ProxyLog_record  
GROUP BY 
    ClientUserName, DestHost 

)
SELECT ClientUserName, DestHost, counts
FROM c
WHERE rowNum = 2

(Don't use count(DestHost) counts if it's not required, or use another CTE to save counting twice)

Upvotes: 1

Nigel Coates
Nigel Coates

Reputation: 46

Wjy not just select top 2 and take the aecomd row from esult set?

Upvotes: 0

Chains
Chains

Reputation: 13157

Based on what you've tried so far...

SELECT top 1
    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

You could use ROW_NUMBER()...

You could use a cursor...

You could put an identifier in the top row to allow you to filter it out (and then subsequently, select the top 1 again and you'd have it).

Otherwise, if there is something identifiable about the 2nd row (does it have the highest something, or the lowest, or the most recent date, etc.),

Upvotes: 0

Firoz Ansari
Firoz Ansari

Reputation: 2515

Try:

SELECT
    ClientUserName, DestHost, count(DestHost) counts  
FROM 
    #ProxyLog_record  
WHERE
    ClientUserName = (    
        ;with cte as 
        (
            select ROW_NUMBER() over (order by ClientUserName) as rn, * 
            from #ProxyLog_count_2
        ) 
        select ClientUserName from cte where rn=2    
    )
GROUP BY 
    ClientUserName, DestHost 
ORDER BY 
    counts DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Presumably, you are using SQL Server. The "top 1" is selecting a random row from the table, because you have no order by clause.

If you want the second row inserted into the table, the way to do this is to define an auto-incrementing column in the table. The create table statement should include:

create table #ProxyLog_count_2 (
    ProxyLog_Count_2_id int not null identity(1,1),
    ...
)

You can then get the second row inserted by using the simple where clause:

where ProxyLog_Count_2_id = 2

Upvotes: 1

Related Questions