max with groupby - 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)

max with groupby

Author

Topic

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-04-08 : 11:32:50

i have one query:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY DATEADD(dd, DATEDIFF(dd, 0, salesdate), 0)
order by salestitle

i m getting more than 1000 rows.. in output
some of them are :

salesid salestitle
---------------------------------
1187 1/20/2005 9:45:54 AM
82162 2K8_KTBcalendar_pre1 9/19/2007 10:46:02 AM
82496 2K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
91857 2K8_KTBcalender_inst 10/16/2007 2:47:31 PM
175660 AmerHeartMo_2008 2/22/2008 1:05:24 PM
179614 AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554 American_Heart_Month_Feb2005
25335 Cholesterol_Educ_Mo_2006_Rem_test
25186 Cholesterol_Educ_Mo_2006_Rem_test
25282 Cholesterol_Educ_Mo_2006_Rem_test

i want (desired)output like: -
1187 1/20/2005 9:45:54 AM
82496 2K8_KTBcalendar_pre1 9/20/2007 3:29:44 PM
91857 2K8_KTBcalender_inst 10/16/2007 2:47:31 PM
179614 AmerHeartMo_2008 2/26/2008 4:54:18 PM
2554 American_Heart_Month_Feb2005
25335 Cholesterol_Educ_Mo_2006_Rem_test

i want max(salesid) among that same salestitle

when i m doing like:
SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY substring([salestitle],1,charindex(' ',[salestitle])-1)

i m getting an error
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 get correct output.

thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:36:44

[code]SELECT SalesID,
SalesTitle,
SalesDate
FROM (
SELECT SalesID,
SalesTitle,
SalesDate,
ROW_NUMBER() OVER (PARTITION BY SalesTitle ORDER BY SalesDate DESC) AS RecID
FROM SalesHistory
) AS d
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:38:58

Make sure you always have a space in salestitle field.Else use:-

SELECT MAX(salesid) AS salesid, max(salestitle) as salestitle
FROM saleshistory s where list = 'sales'
GROUP BY case when charindex(' ',[salestitle])>0 
then substring([salestitle],1,charindex(' ',[salestitle])-1)
else [salestitle]
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:40:02

And as always, what make you think MAX(SalesID) and MAX(SalesTitle) derives from same record?

E 12°55'05.25"
N 56°04'39.16"

sqlhelp14
Yak Posting Veteran

55 Posts

Posted - 2008-04-08 : 11:41:17

thanks peso & visakh16.

now its working perfect.
i got results and also knew about row_number() function.
thanks both of you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 11:43:32

Use Visakh's suggestion.
You should always try to make things as difficult as possible.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:56:27

quote:
Originally posted by Peso

Use Visakh's suggestion.
You should always try to make things as difficult as possible.

E 12°55'05.25"
N 56°04'39.16"

I was just suggesting a way for preventing the error OP posted.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:57:21

quote:
Originally posted by Peso

And as always, what make you think MAX(SalesID) and MAX(SalesTitle) derives from same record?

E 12°55'05.25"
N 56°04'39.16"

I think OP was asking for maximum value of salesid and title rather than details from the max record.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 03:17:08

quote:
Originally posted by visakh16

quote:
Originally posted by Peso

And as always, what make you think MAX(SalesID) and MAX(SalesTitle) derives from same record?

E 12°55'05.25"
N 56°04'39.16"

I think OP was asking for maximum value of salesid and title rather than details from the max record.

The confusion is that OP used the code which wont produce the expected result

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 03:17:54

quote:
Originally posted by sqlhelp14

thanks peso & visakh16.

now its working perfect.
i got results and also knew about row_number() function.
thanks both of you.

And see what you can do with ROW_NUMBER() function
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources