CODEWITHSUNDEEP
sqlsql-server
Carmen C
Carmen C

Reputation: 55

How to group SQL Value if the column is same value

Needed help with SQL, tried for a while and still cant figure it out

MY Create Table

CREATE TABLE Data
(
    Sender              VARCHAR(255),
    Receiver            VARCHAR(255),
    MSG                 VARCHAR(255),
);

After Insert VALUES

SEND1   REC1    Hey whats up
SEND1   REC2    Yoo how are you
SEND1   REC1    Nothing much

What I've been trying to do is to 

Select * FROM DATA WHERE Sender = 'SEND1'

However, I just wanted to show the second row and third row, basically if the SENDER and RECEIVER is same as previous row, ignore the previous row and took the last row only and no duplication of the same same SENDER and RECEIVER

Thanks

I am using SQL-SERVER

What I meant is to always take the last row of the same 'Sender' and 'Receiver', the first row and third row has same sender and receiver, so when I select statement, I just want the last one

Upvotes: 0

Views: 178

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93754

As per comments in question, considering you have an identity column to find the order of records. Use ROW_NUMBER window function to find the latest Sender/Receiver message.

Select * from
(
Select Row_Number()Over(Partition by Sender,Receiver order by Num desc) Rn,*
From yourtable
) A
Where Rn = 1

Upvotes: 1

SCanova
SCanova

Reputation: 48

As you were suggested in the comments you can add an integer autoincrementing column Num in this way:

CREATE TABLE Data
(
Num integer IDENTITY(1,1),
Sender              VARCHAR(255),
Receiver            VARCHAR(255),
MSG                 VARCHAR(255),
);

Hence, the latest message has the highest number. Then you add values:

insert into Data values ('SEND1','REC1','Hey whats up')

insert into Data values ('SEND1','REC2','Yoo how are you')

insert into Data values ('SEND1','REC1','Nothing much')

Finally, you should try to run this query:

select * from Data
where Num in (
  select max(Num) from Data 
      group by Sender, Receiver)

Upvotes: 1

WJS
WJS

Reputation: 724

You could try using the OFFSET clause, to offset (skip) the first message.

SELECT * FROM Data WHERE Sender = 'SEND1' OFFSET 1;

The trouble is you need will an id column or a datetimestamp to ORDER BY so you can guarantee the first message sent is not included.

e.g.

SELECT 
* 
FROM Data
WHERE Sender = 'SEND1' 
ORDER BY id -- or a datetime column
LIMIT 1000 OFFSET 1;

I don't quite understand:

basically if the SENDER and RECEIVER is same as previous row, ignore the previous row and took the last row only and no duplication of the same same SENDER and RECEIVER

But this should cover the case:

I just wanted to show the second row and third row

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42813

Assuming that you have unique/PK id column and higher value means latest record. Then you can use:

select DATA.* from DATA 
join(
    select max(id) as maxid 
    from DATA 
    group by Sender, Receiver 
)t
on DATA.id = t.maxid

Upvotes: -1

Related Questions