SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help Needed with Max syntax


Help Needed with Max syntax

Author
Message
gatorfe
gatorfe
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 21
Hello we have a detail table in our erp systm that generates timestamp and message info per order. We are trying to create syntax so we can show the end user summarized data. For example this below should be the output we generate through the syntax based on the picture of the detail table included.

Order_No TimeStamp Message
67009 2013-10-02 08.43 Order not set for auto-invoice in SVKECH
67023 2013-10-02 08.43 Order in-use
67023 2013-10-02 08.43 Order not set for auto-invoice in SVKECH
109592 2013-09-06  10.27 Order in-use
117578 2013-09-06  10.27 Failed Logistics Check
117578 2013-12-12 13.18 Called SNM505C.PC00117578
119171 2013-07-05 12.59 Order not set for auto-invoice in SVKECH

This is the syntax we have so far,
SELECT order_number, MAX(TIMESTAMP) AS Recent, Message FROM LOG Table
group by order_number

Any suggestions would be greatly appreciated! Thank you for your time!
Attachments
Detail ERP Table.jpg (10 views, 298.00 KB)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17324 Visits: 19122
It seems that you just need to add the column Message to your group GROUP BY statement.;-)


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27132 Visits: 17557
gatorfe (1/16/2014)
Hello we have a detail table in our erp systm that generates timestamp and message info per order. We are trying to create syntax so we can show the end user summarized data. For example this below should be the output we generate through the syntax based on the picture of the detail table included.

Order_No TimeStamp Message
67009 2013-10-02 08.43 Order not set for auto-invoice in SVKECH
67023 2013-10-02 08.43 Order in-use
67023 2013-10-02 08.43 Order not set for auto-invoice in SVKECH
109592 2013-09-06  10.27 Order in-use
117578 2013-09-06  10.27 Failed Logistics Check
117578 2013-12-12 13.18 Called SNM505C.PC00117578
119171 2013-07-05 12.59 Order not set for auto-invoice in SVKECH

This is the syntax we have so far,
SELECT order_number, MAX(TIMESTAMP) AS Recent, Message FROM LOG Table
group by order_number

Any suggestions would be greatly appreciated! Thank you for your time!


Hi and welcome to the forums. It would greatly help your posts if you provide readily consumable ddl and sample data.

Something like this:


create table #Log
(
Order_Number int,
LogDate datetime,
LogMessage varchar(200)
)

insert #Log
select 67009, '2013-10-02 08:43', 'Order not set for auto-invoice in SVKECH' union all
select 67023, '2013-10-02 08:43', 'Order in-use' union all
select 67023, '2013-10-02 08:43', 'Order not set for auto-invoice in SVKECH' union all
select 109592, '2013-09-06 10:27', 'Order in-use' union all
select 117578, '2013-09-06 10:27', 'Failed Logistics Check' union all
select 117578, '2013-12-12 13:18', 'Called SNM505C.PC00117578' union all
select 119171, '2013-07-05 12:59', 'Order not set for auto-invoice in SVKECH'



I changed a couple of your column names because using reserved words as column names is painful to work with.

The issue you are facing is you want to have a single row for each order but you also have LogMessage in the output. Which message do you want?

This is the proper syntax but it will not return what you are looking for.


SELECT order_number, MAX(LogDate) AS Recent, LogMessage
FROM #Log
group by order_number, LogMessage




What is the expected output based on the sample data?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Luis Cazares
Luis Cazares
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17324 Visits: 19122
Sean Lange (1/16/2014)


What is the expected output based on the sample data?

Hey Sean,
It seems that the OP wrote the expected results and attached the sample data. So adding message column to the group by seems to work fine.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27132 Visits: 17557
Luis Cazares (1/16/2014)
Sean Lange (1/16/2014)


What is the expected output based on the sample data?

Hey Sean,
It seems that the OP wrote the expected results and attached the sample data. So adding message column to the group by seems to work fine.


Ahh yes. I didn't look at the picture because...well...because so often the attached pictures provide little to the discussion. In this case apparently it did. :-D

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
gatorfe
gatorfe
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 21
Thank you all for the suggestions! :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search