finding minimum value

How to find the minimum value from time Timestamp column ?

I want get the eralier timestamp vlaues from the avaliable list

when iam using Aggregate transformation ..its again giving all the list of vlaues

Thanks
Niru

If you have only one minimum per data set I would use the Script component to get it manually.

Thanks.

|||no i have lot of set of duplicate values with in the same column....I have to get the Minimum ones in those sets ..like

5
5
3
3

Desired result:

5
5

|||

I still do not understand your scenario.

If you have a data like this:

A B

1 1

1 2

2 7

2 8

You can use the Aggregate component to group on column A and find a minimum on B, so it would produce:

A B

1 1

2 7

Is that what you are looking for?

Find minimum value in a list

How do I find the minimum value in a list if provided in a function?
For example: FindMin('323', '29', '991')
The answer should be '29'.
I could pull this off by comparing each value to the other until I determine
which is the least value. Although I have experience in other languages, I'
m
a bit new to SQL and am a bit stumped on how to pull this one off. I'm
assuming it should be rather easy.
PatrickMin() function
e.g. SELECT MIN(field1) FROM Table1
-Jason
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I
determine
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Create a user defined function that split the list and return a table and us
e
it as the source to:
select min(colA)
from dbo.ufn_table_from_list('323, 29, 991')
go
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
Faking arrays in T-SQL stored procedures
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
AMB
"Patrix317" wrote:

> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I determi
ne
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Jason:
Thanks, Jason, but this isn't quite what I'm looking for. I'm passing three
separate felds not one.
Example record:
Farmer: John
Apples: 323
Oranges: 29
Peache: 991
Of all of John's produce, he has the least in oranges.
FindMin(Apples, Oranges, Peaches)
Answer: Oranges
Hope this clears up my question.
Patrick
"Jason Mauss" wrote:

> Min() function
> e.g. SELECT MIN(field1) FROM Table1
> -Jason
> "Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
> news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> determine
> I'm
>
>|||What format is the data in? If it is a column in a database then
SELECT TOP 1 MyValue
FROM MyTable
ORDER BY MyValue
If it's in a string list, then it isn't properly normalized and you may have
issues with your database design. You should probably give a little more
information.
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I
> determine
> which is the least value. Although I have experience in other languages,
> I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Or that. DUH.
"Jason Mauss" <jason.mauss@.nospamgmail.com> wrote in message
news:egHAUAJNFHA.3708@.TK2MSFTNGP14.phx.gbl...
> Min() function
> e.g. SELECT MIN(field1) FROM Table1
> -Jason
> "Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
> news:6DF760EE-FF9B-4577-A7B9-76A45A7A71C3@.microsoft.com...
> determine
> I'm
>|||Patrick,
A UDF to split a delimited list and return a table, as suggested by Alej,
is:
-- *********************************
Create Function dbo.ParseString (
@.S VarChar(8000), @.delim Char(1))
Returns @.tOut Table
(ValNum Integer Primary Key Identity,
sVal VarChar(1000))
As
Begin
Declare @.sVal VarChar(1000)
Declare @.dPos Integer
Declare @.Start Integer Set @.Start = 1
-- --
If @.S = @.delim Or Len(@.S) = 0 Return
Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
-- --
Set @.dPos = CharIndex(@.delim, @.S, 1)
While @.dPos <> 0
Begin
Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
Insert @.tOut (sVal) Values (@.sVal)
Set @.Start = @.dPos + 1
Set @.dPos = CharIndex(@.delim, @.S, @.Start)
End
Return
-- ---
End
-- *********************************
Then you can just take the minimum of the values in the table returned by
this function..
Select Min(Cast(sVal as Integer))
From dbo.ParseString('323, 29, 991', ',')
"Patrix317" wrote:

> How do I find the minimum value in a list if provided in a function?
> For example: FindMin('323', '29', '991')
> The answer should be '29'.
> I could pull this off by comparing each value to the other until I determi
ne
> which is the least value. Although I have experience in other languages,
I'm
> a bit new to SQL and am a bit stumped on how to pull this one off. I'm
> assuming it should be rather easy.
> Patrick|||Woops, sorry about that...didn't entirely understand what you were looking
for. Alejandro's suggestion is probably what you're looking for.
-Jason
"Patrix317" <Patrix317@.discussions.microsoft.com> wrote in message
news:C5DCE464-E3FB-4E3F-A02A-2D5FF3BDCED6@.microsoft.com...
> Jason:
> Thanks, Jason, but this isn't quite what I'm looking for. I'm passing
three
> separate felds not one.
> Example record:
> Farmer: John
> Apples: 323
> Oranges: 29
> Peache: 991
> Of all of John's produce, he has the least in oranges.
> FindMin(Apples, Oranges, Peaches)
> Answer: Oranges
> Hope this clears up my question.
> Patrick
> "Jason Mauss" wrote:
>
languages,
I'm|||Bob,
Please see my reply to Jason for more information.
"Bob Castleman" wrote:

> What format is the data in? If it is a column in a database then...|||CBretana,
Thanks for the code. It's not as cryptic as I had suspected. I'm going to
try this now and check it out. It's certainly a different way of handling i
t!
Patrick
"CBretana" wrote:
> Patrick,
> A UDF to split a delimited list and return a table, as suggested by Ale
j,
> is:
> -- *********************************
> Create Function dbo.ParseString (
> @.S VarChar(8000), @.delim Char(1))
> Returns @.tOut Table
> (ValNum Integer Primary Key Identity,
> sVal VarChar(1000))
> As
> Begin
> Declare @.sVal VarChar(1000)
> Declare @.dPos Integer
> Declare @.Start Integer Set @.Start = 1
> -- --
> If @.S = @.delim Or Len(@.S) = 0 Return
> Else If Right(@.S,1) <> @.Delim Set @.S = @.S + @.Delim
> -- --
> Set @.dPos = CharIndex(@.delim, @.S, 1)
> While @.dPos <> 0
> Begin
> Set @.sVal = LTrim(Substring(@.S, @.Start, @.dPos - @.Start))
> Insert @.tOut (sVal) Values (@.sVal)
> Set @.Start = @.dPos + 1
> Set @.dPos = CharIndex(@.delim, @.S, @.Start)
> End
> Return
> -- ---
> End
> -- *********************************
>
> Then you can just take the minimum of the values in the table returned by
> this function..
> Select Min(Cast(sVal as Integer))
> From dbo.ParseString('323, 29, 991', ',')
> "Patrix317" wrote:
>

find Minimum in range thats not in Table?

Hi Folks,

any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.

Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so

I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.

thanks in advance!could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?

rudy|||Originally posted by r937
could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?
rudy

Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.

'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.

I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.

thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name

if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:

-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match

each of these requires some way of specifying the set of things that aren't there

you don't actually have to have an auxiliary table, though

try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy

darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(

thanks,

zaphod|||yeah, but i didn't know i was right at the time!

meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype

rudy