CODEWITHSUNDEEP
mysql
mansi
mansi

Reputation: 877

how to exclude first and last row if group it on particular id

I have sample table with data like this 

id uniqueid values
1   6          0
2   6          1
3   6          2
4   6          0
5   6          1

I want result like this

id uniqueid values
2   6          1
3   6          2
4   6          0

I tried like this

select id,uniqueid,values
FROM t1
WHERE 
 id not in(SELECT concat(MAX(message_id_pk),',',min(message_id_pk)) FROM t1
  where uniqueid=6)
  and `uniqueid`=6
 GROUP BY uniqueid

but its not working

Upvotes: 1

Views: 428

Answers (3)

Ullas
Ullas

Reputation: 11556

Also you can do it by using 2 sub-queries with EXISTS to exclude the min and max id of each uniqueid.

Query

select `id`, `uniqueid`, `values`
from `your_table_name` t1
    where exists (
    select 1 from `your_table_name` t2
    where t2.`uniqueid` = t1.`uniqueid`
    and t2.`id` > t1.`id`
)
and exists(
    select 1 from `your_table_name` t2
    where t2.`uniqueid` = t1.`uniqueid`
    and t2.`id` < t1.`id`
);

Here is a sql fiddle demo

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can achieve the desired results by doing self join, Inner query will get the the max and min ids for per group and outer query will filter out the results by using minid and maxid

select a.*
from demo a
join (
  select `uniqueid`,min(id) minid, max(id) maxid
  from demo
  where uniqueid=6
  group by `uniqueid`
) b using(`uniqueid`)
where a.id > b.minid and a.id < b.maxid /* a.id <> b.minid and a.id <> b.maxid  */

Demo

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Try this - 

SELECT id, uniqueid, values
FROM YOUR_TABLE
WHERE id NOT IN (MIN(id), MAX(id));

Upvotes: -1

Related Questions