Min,Max Function - 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

Min,Max Function

Author

Topic

soofihussain
Starting Member

11 Posts

Posted - 2014-09-07 : 07:59:20

Hi

I have below table i want to find the min and max PH of Pond by Trans date by Farm (Here i display one farm but in my table more farm like 106,107,108... and trans date every day)each of farm with specific date i want to know the pond with min and max of ph
Farm Pond Trans Date Ph
106 06PA03 2014-09-01 00:00:00.000 8.2
106 06PB04 2014-09-01 00:00:00.000 8.2
106 06PB05 2014-09-01 00:00:00.000 8.1
106 06PC01 2014-09-01 00:00:00.000 7.9
106 06PC02 2014-09-01 00:00:00.000 8
106 06PC03 2014-09-01 00:00:00.000 8.1
106 06PC07 2014-09-01 00:00:00.000 7.8
106 06PC08 2014-09-01 00:00:00.000 8.1

result

Farm Pond Trans Date Min Ph Max Ph
106 06PC07 2014-09-01 00:00:00.000 7.8
106 06PA03 2014-09-01 00:00:00.000 8.2
106 06PB04 2014-09-01 00:00:00.000 8.2

regards
Soofi

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-07 : 09:05:59

What have you tried so far?

soofihussain
Starting Member

11 Posts

Posted - 2014-09-07 : 09:38:07

i tried by using subquery with aggregate function in sql server

soofihussain
Starting Member

11 Posts

Posted - 2014-09-07 : 09:40:13

Hi

I have below table i want to find the min and max PH of Pond by Trans date by Farm (Here i display one farm but in my table more farm like 106,107,108... and trans date every day)each of farm with specific date i want to know the pond with min and max of ph by using subquery with aggregate function in sql server
Farm Pond Trans Date Ph
106 06PA03 2014-09-01 00:00:00.000 8.2
106 06PB04 2014-09-01 00:00:00.000 8.2
106 06PB05 2014-09-01 00:00:00.000 8.1
106 06PC01 2014-09-01 00:00:00.000 7.9
106 06PC02 2014-09-01 00:00:00.000 8
106 06PC03 2014-09-01 00:00:00.000 8.1
106 06PC07 2014-09-01 00:00:00.000 7.8
106 06PC08 2014-09-01 00:00:00.000 8.1

result will be

Farm Pond Trans Date Min Ph Max Ph
106 06PC07 2014-09-01 00:00:00.00 7.8
106 06PA03 2014-09-01 00:00:00.000 8.2
106 06PB04 2014-09-01 00:00:00.000 8.2

regards
Soofi

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-07 : 09:54:09

I mean, can you please post the query you've tried. We can start with that.

soofihussain
Starting Member

11 Posts

Posted - 2014-09-07 : 10:02:15

hi
I can't get the query. kindly post the query

soofihussain
Starting Member

11 Posts

Posted - 2014-09-07 : 10:10:08

hi

i don't know to write the query. kindly post the query

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-07 : 14:53:19

Oh, I thought you had something basic in place. (It's best to give it a try before posting -- especially for homework questions, which this looks like).

anyway, something like this should get you going:


select farm, pond, [Trans date], min(ph) 'Min Ph', min(py) 'Min Ph'
from mytable
group by farm, pond, [Trans Date]
order by farm, pond, [Trans Date]

If this doesn't completely solve your problem, use it as a starting point and play with it until it works.

soofihussain
Starting Member

11 Posts

Posted - 2014-09-08 : 04:09:08

I tried below query
SELECT [Farm]
,[Pond]
,[Trans Date]
,min([Ph AM])as [Min PH]
FROM [intelliviewdb].[dbo].[dailyProcesscontparam]
where [Farm]='106' and [Trans Date]='2014-09-01 00:00:00.000'
group by [Farm],[Pond],[Trans Date]
order by [Farm],[Pond],[Trans Date]

But it returns
Farm Pond Trans Date PH
106 06PA03 2014-09-01 00:00:00.000 8.2
106 06PB04 2014-09-01 00:00:00.000 8.2
106 06PB05 2014-09-01 00:00:00.000 8.1
106 06PC01 2014-09-01 00:00:00.000 7.9
106 06PC02 2014-09-01 00:00:00.000 8
106 06PC03 2014-09-01 00:00:00.000 8.1
106 06PC07 2014-09-01 00:00:00.000 7.8
106 06PC08 2014-09-01 00:00:00.000 8.1

But my output will be

Farm Pond Trans Date Min Ph Max Ph
106 06PC07 2014-09-01 7.8
106 06PA03 2014-09-01 8.2
106 06PB04 2014-09-01 8.2

Kindly help

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 05:56:12

Your output sampl has no mnax pH. Is that what you want?

soofihussain
Starting Member

11 Posts

Posted - 2014-09-08 : 06:38:58

SELECT [Farm]
,[Pond]
,[Trans Date]
,min([Ph AM])as [Min PH]
,max([Ph AM])as [Max PH]
FROM [intelliviewdb].[dbo].[dailyProcesscontparam]
where [Farm]='106' and [Trans Date]='2014-09-01 00:00:00.000'
group by [Farm],[Pond],[Trans Date]
order by [Farm],[Pond],[Trans Date]

But it returns
Farm Pond Trans Date Min PH Max PH
106 06PB04 2014-09-01 00:00:00.000 8.2 8.2
106 06PB05 2014-09-01 00:00:00.000 8.1 8.1
106 06PB06 2014-09-01 00:00:00.000 8.2 8.2
106 06PC01 2014-09-01 00:00:00.000 7.9 7.9
106 06PC02 2014-09-01 00:00:00.000 8 8
106 06PC03 2014-09-01 00:00:00.000 8.1 8.1
106 06PC04 2014-09-01 00:00:00.000 8 8
106 06PC05 2014-09-01 00:00:00.000 8 8
106 06PC06 2014-09-01 00:00:00.000 8 8
106 06PC08 2014-09-01 00:00:00.000 8.1 8.1

But my output will be

Farm Pond Trans Date Min Ph Max Ph
106 06PC07 2014-09-01 7.8
106 06PA03 2014-09-01 8.2
106 06PB04 2014-09-01 8.2

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 08:15:19

Where does this line come from in your output:


106 06PA03 2014-09-01 8.2

?

Perhaps you should post your input data. Here's how to do that:

1. Post CREATE TABLE statement for the table dailyProcesscontparam
2. Post INSERT INTO statements to populate that table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources