SQL query help

30 Sep 2005 2:52 PM

Mike Read

30 Sep 2005 2:57 PM

Alexander Kuznetsov

if distance is not nullable,

select ID, distance from s
where not exists(select 1 from s s1 where s1.id=s.id and
s1.distance<s.distance)

how do you know that null is the minimum id there are toher rows with
id=4?

30 Sep 2005 3:54 PM

Mike Read

30 Sep 2005 4:14 PM

Alexander Kuznetsov

worked for me:

create table #t(id int, distance int)
insert into #t values(1, 5)
insert into #t values(1, 1)

insert into #t values(2, 2)
insert into #t values(2, 3)

insert into #t values(3, null)

select ID,    distance from #t s
where not exists(select 1 from #t s1 where s1.id=s.id and
s1.distance<s.distance)

ID          distance
----------- -----------
1           1
2           2
3           NULL

(3 row(s) affected)

drop table #t

30 Sep 2005 4:25 PM

Mike Read

30 Sep 2005 4:40 PM

Alexander Kuznetsov

select DISTINCT ID, distance

30 Sep 2005 4:44 PM

Mike Read

30 Sep 2005 4:00 PM

Mike Read

30 Sep 2005 9:07 PM

Hugo Kornelis

On Fri, 30 Sep 2005 15:52:44 +0100, Mike Read wrote:

Show quote

>Hi
>
>I have a table of IDs and distances eg
>
>ID    distance
>1        0.2
>1        0.1
>2        0.3
>2        0.35
>3        0.2
>3        0.2
>4        null
>
>I like to pull out the row for each ID having the minimum distance (for a
>given ID).
>
>i.e I'd like my resultset to be
>
>1        0.1
>2        0.3
>3        0.2
>4        null
>
>Can anyone help with the required SQL.
>
>Thanks
>   Mike

Hi Mike,

SELECT ID, MIN(distance)
FROM YourTable

?

Best, Hugo

--

(Remove _NO_ and _SPAM_ to get my e-mail address)

2 Dec 2005 7:43 PM

avode

Mike, you can try this way:

SELECT id, NULLIF(MIN(COALESCE(distance, 0)), 0)
FROM (SELECT 1, 0.2 UNION ALL
        SELECT 1, 0.1 UNION ALL
        SELECT 2, 0.3 UNION ALL
        SELECT 2, 0.35 UNION ALL
        SELECT 3, 0.2 UNION ALL
        SELECT 3, 0.2 UNION ALL
--        SELECT 4, 0.05 UNION ALL
        SELECT 4, NULL) AS T(id,distance)
GROUP BY id