1 to 1 2

[simian336]

I have a 1 to many parent to child table relationship. But I need to return a 1 to 1 with the first matching record, regardless of wheather there are more then one matching.

Ex
Table a
ID
3062
3063

ID FK
3062 6897
3062 6935
3062 6936
3062 6937
3062 6938
3063 6898
3063 6939

Possible Answer
3062 6897
3063 6898

Thanks Simi

 

[gmmastros]

Select Id, Min(FK) As FK
From   YourTable
Group By ID

Note that this will not necessarily be the "first" KF, but it is guaranteed to be the minimum FK, which is probably the first, but not necessarily.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SkipVought]

How about MIN(FK)?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

BTW, a table is an UNORDERED relation, so there really is no FIRST row in a group.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[markros]

select ID, FK from (select ID, FK, row_number over (partition by ID order by (select null)) as RowNum) X where RowNum = 1

This is SQL Server 2005 and up solution.

 

[markros]

In my solution I forgot the FROM myTable part after as RowNum.

 

[simian336]

Thanks Guys.. I may have simpified my problem to much.

I have the following query....

select a.id as rpid, b.id as ttid, c.id as tseid,
a.startdate, b.info1, b.info2 as bi2, c.info2 as ci2
from reportperiod a
join tasktimesheet b
on a.id=b.reportperiodid
join dbo.timesheetentry c
on b.id=c.tasktimesheetid
where startdate ='12/7/2009' and b.info1 is not null and b.info2 is not null
and c.info2 is not null

which produced the following output...

rpid ttid tseid startdate info1 bi2 ci2
----- ---- ----- ----------------------- ----------------------------- --- -----------
5606 3062 6897 2009-12-07 00:00:00.000 00001 - ANCHORS 6 IN AND 8 IN 5 COMPANY USE
5606 3062 6936 2009-12-07 00:00:00.000 00001 - ANCHORS 6 IN AND 8 IN 5 USED
5606 3063 6898 2009-12-07 00:00:00.000 00001 - MATERIAL AND SUPPLIES 2 USED
5606 3063 6939 2009-12-07 00:00:00.000 00001 - MATERIAL AND SUPPLIES 2 LEASE

I need to return the lowest 2 tseid, 1 for each ttid, like.

ttid tseid
3062 6936
3063 6939

I tried

with summary AS
(
select a.id as rpid, b.id as ttid, c.id as tseid,
a.startdate, b.info1, b.info2 as bi2, c.info2 as ci2
from reportperiod a
join tasktimesheet b
on a.id=b.reportperiodid
join dbo.timesheetentry c
on b.id=c.tasktimesheetid
where startdate ='12/7/2009' and b.info1 is not null and b.info2 is not null
and c.info2 is not null
)
select summary.ttid, min(tseid) as mintseid
from summary
group by summary.ttid, tseid

but that gives me all 4 records like...

ttid mintseid
----------- -----------
3062 6897
3062 6936
3063 6898
3063 6939

Thanks

Simi

 

[markros]

Remove TseID from GROUP BY condition!

Also please review Row_Number() solution from above.

 

[simian336]

Perfect... Thanks Markros

Simi

 

[markros]

No problem