combining MAX MIN stuff - 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

combining MAX MIN stuff

Author

Topic

palace
Starting Member

3 Posts

Posted - 2012-04-05 : 10:33:50

Hi There
I'm trying to extract some max and min data into a summary table for a report.
I have 2 tables
MSGLOGS with 4 columns
MsgID int
MsgSize int
RecipientCount int
Sender int

Senders with 2 columns
id int
Sender varchar(50)

I can get the summary data from MSGLOGS with
SELECT
MAX(ml.MsgSize) AS MaxMsgSize,
MIN(ml.MsgSize) AS MinMsgSize,
MAX(ml.RecipientCount) AS MaxRcpt,
MIN(ml.RecipientCount) AS MinRcpt
FROM
dbo.MsgLogs ml

I would like to get 1 table that shows say 4 rows that have the max and min values with the associated sender name i.e
MaxSize,1234,Joe
MinSize,22,Bob
MaxRcpt,500,Carol
MinRcpt,7,Alice

I thought about using a union and using something like
select top(1) 'MAXSize',
MsgLogs.MsgSize,
(Select Senders.Sender from Senders where Senders.id=MsgLogs.Sender) as SenderName
from msglogs
order by MsgLogs.MsgSize desc ... etc
but seems like you can't union if you have a order by

The only way I can think is to create some views and union those using a select top(1) from each of the views.

Anyway it's getting out of my level of knowledge so if someone can help it would be great.

Cheers
Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-05 : 11:44:29

sorry not fully clear. you want max min values per sender or single row with max min over entire table data?

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

kcrinklaw
Starting Member

2 Posts

Posted - 2012-04-05 : 14:04:41

I'd probably separate those values out as invidual variables (ie. @n_maxmsg, @n_minmsg, etc) and set them using same logic. Re-run a select afterwards and use @n_variables inside of WHERE msgSize IN (var,var,var,var) order by msgSize.
You could do what you're trying to do, but you're basically treating a column (message size) as a new row in a new table
converting:
1
2
3
4
into:
1 | 2 | 3 | 4

which you cannot join to without a pivot. Which sucks. I think variables instead of a derived table as your best bet. Not the only solution of course. Let us know what you come up with.

Website Expert

palace
Starting Member

3 Posts

Posted - 2012-04-06 : 03:47:34

Thank you for your replies

Apologies as I might be trying to make something more complicated than is needed...not sure

I have a bunch of message logs that I've imported into SQL. I have 2 tables with the structure above and wanted to get some summary data that I can present on a webpage. Things like which entry has the largest message size and who sent it, same for minimum, which person sent messages to the most number of recipients etc. Here is a snapshot of the data from the 2 tables

MsgLogs
MsgID |RecipientCount |MsgSize|Subject |Sender
--------|---------------|-------|---------------------------------------|------
1 |1 |2771 |DailyEmail Task Completion |1
2 |1 |3407 |TRIPS Task Completion |1
4 |1 |2768 |Daily Reports Task Completion |1
5 |1 |4344 |ITISD Christmas Party |2
6 |1 |22747 |MailGate Report |3
10 |4 |1623 |ScanMail pattern file was updated |4
11 |1 |50075 |24 Bottles of Red Wine |5

Senders
id |Sender
----|------------------------------
1 |DYSReports@myplace.me
2 |joe.bloggs@myplace.me
3 |tom.thumb@mailgate.myplace.me
4 |admins@myplace.me
5 |replies@livingsocial.com

with Senders.id linked to MsgLogs.Sender

I managed to work out the maximum, minimum etc and return that as a table
SELECT
MAX(ml.MsgSize) AS MaxMsgSize,
MIN(ml.MsgSize) AS MinMsgSize
FROM
dbo.MsgLogs ml

which gives me (using above data)
MaxMsgSize |MinMsgSize
------------|-----------
50075 |1623

I then thought I should get the matching sender that goes with the above data and present something like
SummaryType |SummaryData |Who
---------------------------------------------
MaxMsgSize |50075 |replies@livingsocial.com
MinMsgSize |1623 |admins@myplace.me

Where SummaryType is just a text identifier.

I could probably return 2 tables with 2 seperate select queries but a lot of it is just me trying to improve on my SQL skills. I've read some info on rank and partition so looking at that at the moment?

Cheers again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 09:13:18

[code]
SELECT m1.SummaryType,m.MsgSize AS SummaryData,s.Sender AS Who
FROM dbo.MsgLogs m
INNER JOIN (SELECT MAX(MsgSize) AS MsgSize,'MaxMsgSize' AS SummaryType
FROM dbo.MsgLogs
UNION ALL
SELECT MIN(MsgSize),'MinMsgSize'
FROM dbo.MsgLogs
)m1
ON m1.MsgSize = m.MsgSize
INNER JOIN Senders s
ON s.id = m.Sender
[/code]

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

palace
Starting Member

3 Posts

Posted - 2012-04-06 : 11:19:01

Sweet..really appreciate the response...this works a treat. Might take me a little while to fully understand it, but can basically see how it works.

Thanks once again
Peter

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-06 : 15:14:42

welcome

let me know if you need more clarification on anything

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources