SELECT a random row for each different Type

[Yannick Turgeon]

Hello all,

I'm using SS 2000

Based on the following table and data:

CREATE TABLE T (
F1 VARCHAR(1),
F2 INT,
F3 INT)

/* T has no unique key */

/* The data */
INSERT INTO T
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
UNION ALL
SELECT 'B', 3, 11
UNION ALL
SELECT 'A', 0, 0
UNION ALL
SELECT 'B', 2, 5
UNION ALL
SELECT 'D', 6, 7

In the real situation, T is a subquery build from some tables and views. I
cannot easily send the real problem.

I want a query that will return:
- One row for each different F1, no matter which it is.
- Ideally, no temporary table should be created. Only subquery.
- F2 and F3 must come from the same row. So the use of aggregate functions
to retreive each of them cannot be used.

A possible result could be:

F1 F2 F3
---- --------- -----------
A 1 10
B 2 12
D 6 7

How could I do that?

Thanks for your help

Yannick

[David Portas]

Re: SELECT a random row for each different Type

Yannick, this is a repost. Shervin posted an answer under your original
post. Wasn't it suitable?

Here's my variation on Shervin's solution:

SELECT DISTINCT T.f1, T.f2, T.f3
FROM T
JOIN
(SELECT f1, MIN(CAST(f2 AS CHAR)+CAST(f3 AS CHAR)) AS m
FROM T
GROUP BY f1) AS F
ON T.f1 = F.f1 AND CAST(T.f2 AS CHAR)+CAST(T.f3 AS CHAR)=F.m
[color=blue]
> In the real situation, T is a subquery build from some tables and views. I
> cannot easily send the real problem.[/color]

Of course, a better method might be to modify that subquery to give the
correct end result, which was why I suggested you post it...

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

[Yannick Turgeon]

Re: SELECT a random row for each different Type

David (sorry for the previous "Davis"!),

I did not see Shervin's second post. My news server probably had
difficulties because it isn't in the thread. I just went to Google and I've
read it. Yes, his solution works perfectly for my situation.

Thanks for your help to both of you.

Yannick

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:dp-dnQA3RNKlouWiRV n-tQ@giganews.com ...[color=blue]
> Yannick, this is a repost. Shervin posted an answer under your original
> post. Wasn't it suitable?
>
> Here's my variation on Shervin's solution:
>
> SELECT DISTINCT T.f1, T.f2, T.f3
> FROM T
> JOIN
> (SELECT f1, MIN(CAST(f2 AS CHAR)+CAST(f3 AS CHAR)) AS m
> FROM T
> GROUP BY f1) AS F
> ON T.f1 = F.f1 AND CAST(T.f2 AS CHAR)+CAST(T.f3 AS CHAR)=F.m
>[color=green]
> > In the real situation, T is a subquery build from some tables and views.[/color][/color]
I[color=blue][color=green]
> > cannot easily send the real problem.[/color]
>
> Of course, a better method might be to modify that subquery to give the
> correct end result, which was why I suggested you post it...
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>[/color]