query help needed - 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

query help needed

Author

Topic

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-07-28 : 11:03:48

Hi,

I have a table which has client name, date, price in a database table. There will be multiple records for a client with different prices for the same date.
I have 100 records in this table from 07/21/2011 till 07/27/2011.

Now i want to retrieve the minimum price for all clients for each date.

Example:

Client A
Client B
Client C

07212011 - client A - 25
07212011 - client A - 12
07212011 - client A - 50
07212011 - client B - 23
07212011 - client B - 13
07212011 - client C - 24
07212011 - client C - 42

07222011 - client A - 41
07222011 - client A - 24
07222011 - client B - 52
07222011 - client B - 10

07272011 - client B - 93
07272011 - client B - 27
07272011 - client C - 36
07272011 - client C - 25

Result should be :

07212011 - client A - 12
07212011 - client B - 13
07212011 - client C - 24

07222011 - client A - 24
07222011 - client B - 52
07222011 - client B - 10

07272011 - client B - 27
07272011 - client C - 25

How can i get this in a query?

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-28 : 12:17:51

At the risk of doing somebody's homework ...
SELECT Date,ClientName,MIN(Price) as MinimumPrice
FROM yourTable
GROUP BY Date,ClientName

Everyday I learn something that somebody else already knew

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-07-28 : 14:02:51

Thanks Jimf.

Is there any other way without using group by. This works but, What i gave you was only a sample there are several other fields(12) involved.
When i include other fields, the result is getting messed up.

I can handle it on .net side but wanted to check if it can be done on database.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-28 : 14:10:51

Something like this maybe?

Jim

SELECT DISTINCT Date,ClientName,<other Columns>
, MIN(Price) OVER(Partition By Date,ClientName)
FROM yourTable

Everyday I learn something that somebody else already knew

rowter
Yak Posting Veteran

76 Posts

Posted - 2011-07-30 : 01:29:18

Thanks jimf that worked

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources