How could I do that (Query)

[Yannick Turgeon]

Hello all,

I'm using SS 2000

Based on the following query, how could I get only one row for each
different field "F1"? I don't want to use temp table.

----------
SELECT ???
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
----------

One of the possible answer could be:

F1 F2 F3
---- --------- -----------
B 3 11 /* One row for "B" */
A 4 10 /* One row for "A" */

TIA

Yannick

[Shervin Shapourian]

Your question is not clear. Please tell us the logic you want to follow. How
do you define the value of F2 and F3?

Shervin

"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:N%*********************@news20.bellglobal.com ...

Hello all,

I'm using SS 2000

Based on the following query, how could I get only one row for each
different field "F1"? I don't want to use temp table.

----------
SELECT ???
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
----------

One of the possible answer could be:

F1 F2 F3
---- --------- -----------
B 3 11 /* One row for "B" */
A 4 10 /* One row for "A" */

TIA

Yannick

[David Portas]

Do you mean taht this query represents some data in a table?

If so, and assuming that (f1,f2) is unique:

SELECT T.*
FROM
(SELECT f1, MAX(f2) AS f2
FROM T
GROUP BY f1) AS X
JOIN T
ON T.f1 = X.f1 AND T.f2 = X.f2

--
David Portas
------------
Please reply only to the newsgroup
--

[Erland Sommarskog]

Yannick Turgeon (no****@nowhere.com) writes:

I'm using SS 2000

Based on the following query, how could I get only one row for each
different field "F1"? I don't want to use temp table.

----------
SELECT ???
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
----------

This could do it:

SELECT F1, MIN(F2), MIN(F3)
FROM (
SELECT 'A' AS F1, 1 AS F2, 10 AS F3
UNION ALL
SELECT 'B', 2, 12
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 4, 10) T
GROUP BY F1

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Yannick Turgeon]

In my real situation, the SELECT ... UNION clause is replaced by a SELECT sub
query which return rows without unique key. Two rows could be exactly
the same. It does not matter which row is returned for a F1 but I want one
and only one row for each F1, and F2 and F3 must be from the same row.
This exclude "SELECT F1, MIN(F2), MIN(F3) ..."

I hope is clearer. Thanks for your help.

Yannick
Le Fri, 26 Sep 2003 14:17:09 -0700, Shervin Shapourian a écrit*:

Your question is not clear. Please tell us the logic you want to follow. How
do you define the value of F2 and F3?

Shervin

[Yannick Turgeon]

Davis,

I haven't been clear enough. T is a subquery which return rows without
unique key. Two rows could be exactly the same.

Thanks for your time.

Yannick
Le Fri, 26 Sep 2003 22:22:33 +0100, David Portas a écrit*:

Do you mean taht this query represents some data in a table?

If so, and assuming that (f1,f2) is unique:

SELECT T.*
FROM
(SELECT f1, MAX(f2) AS f2
FROM T
GROUP BY f1) AS X
JOIN T
ON T.f1 = X.f1 AND T.f2 = X.f2

--
David Portas
------------
Please reply only to the newsgroup

[David Portas]

> I haven't been clear enough. T is a subquery

Then post the actual subquery, the DDL for the base tables and some sample
data as INSERT statements. Without that it's difficult to give a full
answer.

--
David Portas
------------
Please reply only to the newsgroup
--

[Shervin]

OK, assuming F2 and F3 are integer values less than 10,000,000,000 this qury
returns what you want. SF2 and SF3 columns of the result set are string
fields, you can convert them back to integer values if you want.

select f1,
left(max(convert(char(10),f2) + convert(char(10),f3)), 10) as SF2,
right(max(convert(char(10),f2) + convert(char(10),f3)), 10) as SF3
from YourSubQuery
group by f1

Shervin

"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:pa****************************@nowhere.com...

In my real situation, the SELECT ... UNION clause is replaced by a SELECT sub query which return rows without unique key. Two rows could be exactly
the same. It does not matter which row is returned for a F1 but I want one
and only one row for each F1, and F2 and F3 must be from the same row.
This exclude "SELECT F1, MIN(F2), MIN(F3) ..."

I hope is clearer. Thanks for your help.

Yannick
Le Fri, 26 Sep 2003 14:17:09 -0700, Shervin Shapourian a écrit :

Your question is not clear. Please tell us the logic you want to follow. How do you define the value of F2 and F3?

Shervin