CODEWITHSUNDEEP
sqlsql-server
user2100632
user2100632

Reputation: 121

Top Row within the Second Table

I'm having an issue with getting TOP to work within my SQL query. I only want to see the first row within the PART_AML table. I'm not having any luck trying to only query that table without querying the PART table.

How can I go about only showing the top row within the PART_AML table? I'm using Microsoft SQL.

Thank you for your help its greatly appreciated.

SELECT  innovator.PART.STATE, 
    innovator.PART.NAME, 
    innovator.PART.ITEM_NUMBER, 
    innovator.PART.ID, 
    innovator.PART.TYPE, 
    innovator.MANUFACTURER_PART.SPEC_URL

FROM innovator.PART     

join innovator.PART_AML
    on innovator.PART_AML.SOURCE_ID = innovator.PART.ID

join innovator.MANUFACTURER_PART
    on innovator.MANUFACTURER_PART.ID = innovator.PART_AML.RELATED_ID

WHERE
(innovator.PART.IS_CURRENT = 1) AND (innovator.PART_AML.IS_CURRENT = 1) AND (innovator.MANUFACTURER_PART.IS_CURRENT = 1)

Current Output

    Number  Name        ID      Type        Spec
    E000836 1k          ID1     Resistor    SPEC 1
    E000836 1k          ID1     Resistor    SPEC 2
    E000836 1k          ID1     Resistor    SPEC 3
    E003455 14.400MHz   ID2     Crystal     SPEC 1
    E003455 14.400MHz   ID2     Crystal     SPEC 2
    E003455 14.400MHz   ID2     Crystal     SPEC 3

Preferred Output

    Number  Name        ID      Type        Spec
    E000836 1k          ID1     Resistor    SPEC 1
    E003455 14.400MHz   ID2     Crystal     SPEC 1

Upvotes: 1

Views: 73

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Also you can use simple GROUP BY clause with MIN() function

SELECT innovator.PART.STATE, 
       innovator.PART.NAME, 
       innovator.PART.ITEM_NUMBER, 
       innovator.PART.ID, 
       innovator.PART.TYPE, 
       MIN(innovator.MANUFACTURER_PART.SPEC_URL) AS SPEC_URL
FROM innovator.PART join innovator.PART_AML on innovator.PART_AML.SOURCE_ID = innovator.PART.ID
                    join innovator.MANUFACTURER_PART on innovator.MANUFACTURER_PART.ID = innovator.PART_AML.RELATED_ID
WHERE (innovator.PART.IS_CURRENT = 1) AND (innovator.PART_AML.IS_CURRENT = 1) AND (innovator.MANUFACTURER_PART.IS_CURRENT = 1)
GROUP BY innovator.PART.STATE, innovator.PART.NAME, innovator.PART.ITEM_NUMBER, innovator.PART.ID, innovator.PART.TYPE

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

You can make use of the ranking function ROW_NUMBER() OVER(ORDER BY ...) to do this:

WITH CTE
AS
(
    SELECT  
      i.STATE, 
      i.NAME, 
      i.ITEM_NUMBER, 
      i.ID, 
      i.TYPE, 
      p.SPEC_URL,
      ROW_NUMBER() OVER(PARTITION BY i.ID
                        ORDER BY p.SPEC_URL) AS Rownum
    FROM innovator.PART AS i
    INNER JOIN innovator.PART_AML          AS a on a.SOURCE_ID = i.ID
    INNER JOIN innovator.MANUFACTURER_PART AS p on p.ID        = a.RELATED_ID
    WHERE i.IS_CURRENT = 1 
      AND a.IS_CURRENT = 1
      AND p.IS_CURRENT = 1
)
SELECT *
FROM CTE
WHERE rownum = 1;

Upvotes: 2

Related Questions