Something like MIN(f1, f2)...?

[Gunnar Vøyenli]

Hi I want to select the minium of two fields:

create table a (f1 int, f2 int, f3 int)

insert into a (f1, f2, f3) values (1,19, 150)
insert into a (f1, f2, f3) values (2,18, 160)
insert into a (f1, f2, f3) values (5, 17, 200)
insert into a (f1, f2, f3) values (3, 18, 250)

I want to select the minimum of the combination of two fields.

In pseudo syntax I want to do something like this
select min(f1, f2) from a where f3 > 160
And I want the result set to be:
3 18
This two result values must come from the same record...
And of course f1 is more significant than f2.
I want to get ONE record in the result set (I do not want to use "order by
f1, f2", because that retrieves ALL the records from the server.)
(The fields f1 and f2 represents date and clock respectively, and I want the
earliest time as the result.)

Any suggestions?
TIA
Gunnar Vøyenli
EDB-konsulent as

[Yannick Turgeon]

Here is my suggestion:

SELECT MIN(f1) f1, MIN(f2) f2
FROM a
WHERE f1 = (SELECT MIN(f1) f1 FROM a WHERE f3 > 160)
AND f3 > 160

HTH

Yannick
"Gunnar Vøyenli" <gv@edbkonsulent_REMOVE_THIS.no> wrote in message
news:40********@news.broadpark.no...

Hi I want to select the minium of two fields:

create table a (f1 int, f2 int, f3 int)

insert into a (f1, f2, f3) values (1,19, 150)
insert into a (f1, f2, f3) values (2,18, 160)
insert into a (f1, f2, f3) values (5, 17, 200)
insert into a (f1, f2, f3) values (3, 18, 250)

I want to select the minimum of the combination of two fields.

In pseudo syntax I want to do something like this
select min(f1, f2) from a where f3 > 160
And I want the result set to be:
3 18
This two result values must come from the same record...
And of course f1 is more significant than f2.
I want to get ONE record in the result set (I do not want to use "order by
f1, f2", because that retrieves ALL the records from the server.)
(The fields f1 and f2 represents date and clock respectively, and I want the earliest time as the result.)

Any suggestions?
TIA
Gunnar Vøyenli
EDB-konsulent as

[David Portas]

Thanks for including the DDL. It helps if you include the primary key in the
DDL as well. That could make a difference to the answer.

Here's one method:

SELECT f1, MIN(f2) AS f2
FROM A
WHERE f1 =
(SELECT MIN(f1)
FROM A
WHERE f3>160)
AND f3>160
GROUP BY f1

You could also use the proprietary TOP modifier:

SELECT TOP 1 f1, f2
FROM A
WHERE f1 IS NOT NULL
AND f2 IS NOT NULL
AND f3>160
ORDER BY f1, f2

I do not want to use "order by
f1, f2", because that retrieves ALL the records from the server.

Not if you use TOP or have a WHERE clause.

--
David Portas
SQL Server MVP
--

[Gunnar Vøyenli]

Thanks to both of you!
Now, I'll make it work!
-Gunnar

"David Portas" <RE****************************@acm.org> wrote in message
news:eO********************@giganews.com...

Thanks for including the DDL. It helps if you include the primary key in the DDL as well. That could make a difference to the answer.

Here's one method:

SELECT f1, MIN(f2) AS f2
FROM A
WHERE f1 =
(SELECT MIN(f1)
FROM A
WHERE f3>160)
AND f3>160
GROUP BY f1

You could also use the proprietary TOP modifier:

SELECT TOP 1 f1, f2
FROM A
WHERE f1 IS NOT NULL
AND f2 IS NOT NULL
AND f3>160
ORDER BY f1, f2

I do not want to use "order by
f1, f2", because that retrieves ALL the records from the server.

Not if you use TOP or have a WHERE clause.

--
David Portas
SQL Server MVP
--