CODEWITHSUNDEEP
mysqlsqloracle-database
hc0re
hc0re

Reputation: 1986

Selecting ONE row per ID with WHERE statement

I am currently writing a SQL statement that should select just one row per ID when meeting requirements.

Let me assume that the table COMPONENTS looks like this:

COMP_ID  DATE_FROM  TIMESTAMP  COMP_TRX_ID
1        10-JAN-14  10-JAN-15  5
1        10-JAN-15  10-JAN-14  5
1        10-JAN-12  10-JAN-13  5
1        11-JAN-16  10-JAN-12  5
2        11-JAN-14  10-JAN-15  5
2        11-JAN-15  10-JAN-14  5
2        11-JAN-12  10-JAN-13  5
2        11-JAN-16  10-JAN-12  5

Now let's assume that the NEXT_DATE from table COMPONENT_STATUS is 01-JAN-15

This is the SELECT statement I currently wrote.

SELECT * FROM (SELECT c.* FROM
COMPONENTS c,
COMPONENTS_TRX ct
WHERE 1=1
AND ct.NAME = 'COMP_77C'
AND c.COMP_ID = ct.COMP_ID
AND c.COMP_ID IN (1,2)
AND trunc(c.DATE_FROM) < (SELECT trunc(ADD_MONTHS((NEXT_DATE),1)) FROM (SELECT NEXT_DATE FROM COMPONENT_STATUS
WHERE COMP_REF_CODE = (SELECT ID FROM REF_CODE WHERE CODE = 'COMP_ACTUAL')
ORDER BY NEXT_DATE DESC)
WHERE ROWNUM = 1
))
ORDER BY DATE_FROM DESC, COMP_TIMESTAMP DESC, COMP_TRX_ID DESC
;

It is returning EVERYTHING from the table COMPONENT, but just properly sorted.

What should I do to make the output look like this:

COMP_ID  DATE_FROM  TIMESTAMP  COMP_TRX_ID
1        10-JAN-15  10-JAN-14  5
2        11-JAN-15  10-JAN-14  5

Containing ONLY the FIRST row that meets the requirements?

Upvotes: 2

Views: 58

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175964

You can use grouping and aggregation function depends on your needs (like MAX):

SELECT  COMP_ID,  MAX(DATE_FROM) AS DATE_FROM,
        MAX(TIMESTAMP) AS TIMESTAMP,  MAX(COMP_TRX_ID) AS COMP_TRX_ID
FROM (SELECT c.* 
      FROM
        COMPONENTS c,
        COMPONENTS_TRX ct
      WHERE 1=1
         AND ct.NAME = 'COMP_77C'
         AND c.COMP_ID = ct.COMP_ID
         AND c.COMP_ID IN (1,2)
         AND trunc(c.DATE_FROM) < (SELECTtrunc(ADD_MONTHS((NEXT_DATE),1)) 
                                  FROM (SELECT NEXT_DATE FROM COMPONENT_STATUS
                                  WHERE COMP_REF_CODE = (SELECT ID 
                                                         FROM REF_CODE 
                                                          WHERE CODE = 'COMP_ACTUAL')
      ORDER BY NEXT_DATE DESC)
WHERE ROWNUM = 1
))
GROUP BY COMP_ID
ORDER BY DATE_FROM DESC, COMP_TIMESTAMP DESC, COMP_TRX_ID DESC;

Try also to avoid comma join syntax (it is obsolete) and use JOIN:

FROM COMPONENTS c,
     COMPONENTS_TRX ct

to:

FROM COMPONENTS c
JOIN COMPONENTS_TRX ct
  ON c.COMP_ID = ct.COMP_ID

Upvotes: 1

Related Questions