Do ORDER BY before GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Do ORDER BY before GROUP BY

Author

Topic

Elfet
Starting Member

4 Posts

Posted - 2011-07-25 : 11:29:30

Hello! Please help me with query!

I have two tables:
Messages(id,user_id,sender_id,text,date)
UsersMessages(user_id,message_id).

How to group this messages by "dialogs"?

Message:
1 1 2 Hello! 2011/07/20
2 2 1 Hi! 2011/07/21
3 3 1 Message... 2011/07/25

UsersMessages:
1 1
1 2
1 3
2 1
2 2
3 3

I need this messages:
3 3 1 Message... 2011/07/25
2 2 1 Hi! 2011/07/21

Ordered by (date) and grouped by (user_id,sender_id)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:48:13

[code]select m.user_id,m.sender_id,m.text,m.date
from messages m
join (select user_id,max(message_id) as msgid from usersmessages group by user_id) u
on u.user_id=m.user_id
and u.msgid = m.id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Elfet
Starting Member

4 Posts

Posted - 2011-07-25 : 12:15:33

And what if i need same for user_id = 1 and limit 2?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 12:21:32

are you using sql 2005 or above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Elfet
Starting Member

4 Posts

Posted - 2011-07-25 : 12:38:41

SQL 2005, yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 12:47:59

use cross apply. see scenario 2 here. use top 2 to get topmost two records

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-26 : 21:54:41

Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you?

Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL.

Why does a message have a sender and not a receiver? Look up the concept of a role in data modeling. Can I send a message to myself? Are you starting to understand how rude and confusing failure to follow Netiquette is? Here is my guess, with keys, constraints and all that stuff

CREATE TABLE Messages
(msg_nbr INTEGER NOT NULL PRIMARY KEY,
sender_user_id INTEGER NOT NULL
REFERENCES Users(user_id),
receiver_user_id INTEGER NOT NULL
REFERENCES Users(user_id),
CHECK (sender_user_id <> receiver_user_id),
msg_text VARCHAR(5000) NOT NULL,
msg_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL);

INSERT INTO Messages
VALUES
(1, 1, 2, 'Hello!', '2011-07-20'),
(2, 2, 1, 'Hi!', '2011-07-21'),
(3, 3, 1, 'Message...', '2011-07-25');

>> How to group this messages by "dialogs"? <<

Please define “dialog” and explain why the “User_Messages” are totally different from mere messages and have to be in another table. My guess is that a dialog might be:

1. occurs on one date or a series of contiguous dates
2. involves two users
3. each one plays the role of sender and of receiver

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Elfet
Starting Member

4 Posts

Posted - 2011-07-27 : 03:23:54

Thank you. I solved my problem: use one more table :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 02:28:32

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources