CODEWITHSUNDEEP
mysqlsqlmariadbgreatest-n-per-group
user11856426
user11856426

Reputation:

How do you query the nth value from a list of values by group? MySQL

I have a table that looks like this:

| thread_id | time       |
| --------- | ---------- |
| 769       | 1566880174 |
| 769       | 1566880415 |
| 769       | 1566884409 |
| 769       | 1566891158 |
| 769       | 1567215554 |
| 769       | 1567227195 |
| 769       | 1567822758 |
| 773       | 1566973984 |
| 773       | 1567216614 |
| 773       | 1567216833 |
| 773       | 1567216913 |
| 773       | 1567216992 |
| 773       | 1567298692 |
| 774       | 1566977378 |
| 774       | 1567218446 |
| 774       | 1567228282 |
| 774       | 1568241410 |
| 784       | 1567300468 |
| 785       | 1567300549 |
| 785       | 1567310667 |
| 785       | 1567310734 |
| 785       | 1567461936 |

and I need to get the nth value grouped by thread_id. If I'm looking for the 2nd row per thread_id, The output should look like this

| thread_id | time       |
| --------- | ---------- |
| 769       | 1566880415 |
| 773       | 1567216614 |
| 774       | 1567218446 |
| 784       | null       |
| 785       | 1567310667 |

How can I achieve this result?

Upvotes: 3

Views: 1270

Answers (5)

Atul
Atul

Reputation: 3347

I have similar situation but need to find the nth value based on the data. Here is the data I have:

----------------
|id |name|score|
----------------
|1  |A   |89   |
|2  |B   |98   |
|3  |C   |88   |
|4  |D   |89   |
|5  |E   |92   |
|6  |F   |90   |
|7  |G   |88   |
|8  |H   |91   |
|9  |I   |90   |
----------------

The requirement is to find the 4th highest score student records. I used this query to get the data based on score:

select student.id, student.name, student.score 
from (select score from student group by score order by score desc limit 3,1) student_temp, student 
where student_temp.score = student.score
order by student_temp.score desc ;

Based on this sql I got the result:

----------------
|id |name|score|
----------------
|6  |F   |90   |
|9  |I   |90   |
----------------

I have basic knowledge of writing sql's. If their any improvement or suggestion, please comment.

Upvotes: 0

user6132211
user6132211

Reputation: 1

A much simpler solution could be the following:

[Assumptions: time is not ordered per thread_id partition]

select distinct thread_id, lead(2, time) over (partition by thread_id rows between unbounded preceding and unbounded following) as time
from table_name

This creates a partition for each thread_id and assigns the second value to all the other rows sharing the same thread_id. All you need is distinct to remove all duplicates.

Upvotes: 0

FanoFN
FanoFN

Reputation: 7114

If you're not on MySQL 8.0, you can do like this (as suggested by @Barmar):

SELECT thread_id,
       CASE WHEN COUNT(*) >=2 
            THEN SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(TIME ORDER BY TIME) ,',',2),',',-1)
             ELSE NULL END AS TIME 
  FROM TableA GROUP BY thread_id;

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can try this below option using row number-

SELECT B.thread_id,C.Time 
FROM (  
    SELECT DISTINCT thread_id FROM your_table A
)B
LEFT JOIN (       
    SELECT *,
        @row_num :=IF(@prev_value = concat_ws('',thread_id),@row_num+1,1)AS RowNumber
        , @prev_value := concat_ws('',thread_id) AS Temp
    FROM your_table A,
    (SELECT @row_num := 0) r
    ORDER BY thread_id,`time`
)C ON B.thread_id = C.thread_id AND C.RowNumber = 2;

Upvotes: 1

Ordec
Ordec

Reputation: 109

SELECT NTH_VALUE(thread_id, 2) OVER  (
    PARTITION BY thread_id
    ORDER BY thread_id DESC
) second, time
FROM table_name;

Should be something like this, if not working show the output

Upvotes: 3

Related Questions