maximum row - select query - 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

SQL Server 2005 Forums

Transact-SQL (2005)

maximum row - select query

Author

Topic

sric
Starting Member

2 Posts

Posted - 2008-08-04 : 10:29:35

Hi All,

i have select query like:

Select max(messageid_) as messageid_, max(title_) as title_
FROM outmail_ c1 where list_ = 'heart_truth_partners'
GROUP BY title_

i m gettting 36rows..some r repetitve title..

example:

messageid title
----------------------
136963 HT_DietCoke 1/6/2008 12:04:15 PM
138513 HT_DietCoke 1/7/2008 10:51:00 AM
138647 HT_DietCoke 1/7/2008 12:22:28 PM
139103 HT_DietCoke 1/7/2008 4:25:55 PM
138340 HT_DietCoke 1/7/2008 9:20:13 AM
154984 HT_Partners 1/28/2008 1:40:47 PM
154890 HT_Partners 1/28/2008 11:11:25 AM
154923 HT_Partners 1/28/2008 11:46:18 AM

so i want results like:

136963 HT_DietCoke 1/6/2008 12:04:15 PM
139103 HT_DietCoke 1/7/2008 4:25:55 PM
154984 HT_Partners 1/28/2008 1:40:47 PM

which is max title..that maxmessageid i want...

if i m modifying my query like:

Select max(messageid_) as messageid_, max(title_) as title_
FROM outmail_ c1 where list_ = 'heart_truth_partners'
GROUP BY substring([title_],1,charindex(' ',[title_])-1)

then i m geeting error as

Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

(0 row(s) affected)

can anybody help me to figure this out..

Thanks a lot friends..

sric
Starting Member

2 Posts

Posted - 2008-08-04 : 10:35:11

i got the solution..

i did like this:

Select max(messageid_) as messageid_, max(title_) as title_
FROM outmail_ c1 where list_ = 'heart_truth_partners'
GROUP BY SUBSTRING(title_, 1, NULLIF(CHARINDEX(' ', title_) - 1, -1))

and i got my solution perfect..

Woohoooooo..

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-04 : 13:26:27

Why are you storing different types of values into same field title?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 05:00:46

Title column should be split to two columns say title and message_Date

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources