How to get only first result in tables join?

Question

I have a query:

SELECT  

    Segment_ID = Segment_ID.Segment_ID,
    Sprav_093.Name as Road_Wear

FROM dbo.Road
LEFT JOIN Segment_ID  ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Driveway_Pavement ON Segment_ID.Segment_ID = Driveway_Pavement.Segment_ID
LEFT JOIN Sprav_093 ON Driveway_Pavement.Kod_Spr093 = Sprav_093.Kod_Spr

Is works fine. There are several row with same Segment_ID in table Driveway_Pavement. So i want get just first of this rows. How can i do it?

add `LIMIT 1` to the end? [SQL Limit/Top](http://stackoverflow.com/questions/971964/limit-10-20-in-sqlserver) — UnholyRanger, Feb 14 '13 at 18:25
What makes one of those rows to be first than the other?, what is the criteria? — Lamak, Feb 14 '13 at 18:25
use `TOP 1` for SQL-Server, SQL Server doesn't support `LIMIT` — rs., Feb 14 '13 at 18:25
@rs: if i write `SELECT TOP 1 Segment_ID.Segment_ID` i get error anyway( — Kliver Max, Feb 14 '13 at 18:37

rs. · Accepted Answer · 2013-02-14T18:40:51.617

Assuming that you want to return one row for each segment id, do this

;WITh CTE AS (
SELECT  

    Segment_ID = Segment_ID.Segment_ID,
    Sprav_093.Name as Road_Wear,
    CASE WHEN Sprav_093.Name IS NOT NULL THEN 
    ROW_NUMBER() OVER (PARTITION BY Segment_ID.Segment_ID 
                       ORDER BY Sprav_093.Name//or date column) 
    ELSE 1 END rn

FROM dbo.Road
LEFT JOIN Segment_ID  ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Driveway_Pavement ON Segment_ID.Segment_ID = Driveway_Pavement.Segment_ID
LEFT JOIN Sprav_093 ON Driveway_Pavement.Kod_Spr093 = Sprav_093.Kod_Spr
)
SELECT * FROM CTE WHERE RN = 1

Yeah its work. But i lost some rows. I have 207 rows in `Segment_ID` but query return 147. — Kliver Max, Feb 14 '13 at 18:34

score 0 · Answer 2 · answered Feb 14 '13 at 18:29

0

Try to apply a MAX function to both selected fields

answered Feb 14 '13 at 18:29

Javier

2,093
35
50

1

Max may not work here. If for instance the rows were 1, 20 and 2, 10 applying max to both rows gives you 2, 20. This means you now have part of two different rows. – Kenneth Fisher Feb 14 '13 at 19:47

score 0 · Answer 3 · answered Feb 14 '13 at 18:44

This should work:

SELECT  

    Segment_ID = Segment_ID.Segment_ID,
    Sprav_093.Name as Road_Wear

FROM dbo.Road
OUTER APPLY (
    SELECT TOP 1 Segment_ID.Segment_ID
    FROM Segment_ID
    WHERE Segment_ID.Road_ID = Road.Road_ID
) Segment_ID  
LEFT JOIN Driveway_Pavement ON Segment_ID.Segment_ID = Driveway_Pavement.Segment_ID
LEFT JOIN Sprav_093 ON Driveway_Pavement.Kod_Spr093 = Sprav_093.Kod_Spr

You can add an ORDER BY to the SELECT inside of the OUTER APPLY if you want a specific "first" row.

How to get only first result in tables join?

3 Answers3