SQL Query Help

Below is the table structure:
Number	ClientID	ClientName	PltVersion	OptCt	DateTimeStarted	DateTimeFZ	RunStatus
10819259	1450	abc	x1	0	6/1/2014 0:03:35	6/1/2014 0:56:06	Success
10819259	1450	abc	x1	0	6/1/2014 0:03:35	6/1/2014 0:56:06	Success
10819260	1450	xyz	x1	1	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	3000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	1	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	0	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819260	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:14:03	Success
10819261	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:34:07	Success
10819261	1450	xyz	x1	4000	6/1/2014 0:03:36	6/1/2014 2:34:07	Success

Open in new window


This below query works but was just wondering if there is a better way of writing this without having to put ClientID, ClientName and other fields that I want to retrieve in the order by clause because they are varchar fields and there may be slight variation in those fields especially in the ClientName field.
SELECT Distinct Number, ClientID, ClientName, PltVer, MIN(DateTimeStarted), MAX(DateTimeFZ), SUM(Images) ImageCt
  FROM [table_name]
  WHERE 
	(DateTimeStarted BETWEEN '2014-09-29' AND '2014-10-05') AND 
	RunStatus IN ('xxx', ''yyy, 'etc')
  GROUP BY RunNumber, ClientID, ClientName, PltVersion
  ORDER BY RunNumber

Open in new window

itsmeharAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
The ORDER BY clause contains the RunNumber only. I guess you were asking about the group by clause. Looks like you want the MIN and MAX partitioned by the RunNumber only but at the same time you want to see all other columns as well. It is easy to achieve with a help of the window functions, like this:
SELECT Distinct RunNumber, ClientID, ClientName, PltVer, 
MIN(DateTimeStarted) OVER (PARTITION BY RunNumber), 
MAX(DateTimeFZ) OVER (PARTITION BY RunNumber), 
SUM(Images) OVER (PARTITION BY RunNumber) ImageCt
  FROM [table_name]
  WHERE 
	(DateTimeStarted BETWEEN '2014-09-29' AND '2014-10-05') AND 
	RunStatus IN ('xxx', ''yyy, 'etc')
  ORDER BY RunNumber

Open in new window

As you can see I have completely removed the GROUP BY clause as it is not required here.
Alternatively you can use the aggregate functions on other columns, like this:
SELECT Distinct RunNumber, Max(ClientID), Max(ClientName), Max(PltVer), MIN(DateTimeStarted), MAX(DateTimeFZ), SUM(Images) ImageCt
  FROM [table_name]
  WHERE 
	(DateTimeStarted BETWEEN '2014-09-29' AND '2014-10-05') AND 
	RunStatus IN ('xxx', ''yyy, 'etc')
  GROUP BY RunNumber
  ORDER BY RunNumber  

Open in new window

itsmeharAuthor Commented:
Is the OVER PARTITION clause supported in SQL2008?
chaauCommented:
According to MSDN and my personal experience it is supported since SQL Server 2005

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itsmeharAuthor Commented:
doesnt work for me
chaauCommented:
Instead of accepting my answer that doesn't work for you I suggest you keep the question open by allowing other experts look into it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.