How to Get Only One Row Per Record in Master Table

How can I get only one row per record in master table?

Firebird 2.0 supports the CASE expression, so you can use:

  SELECT fa.farm AS farm_name,
         MAX(CASE WHEN fa.apple = 1 THEN fa.apple ELSE NULL END AS apple_1,
         MAX(CASE WHEN fa.apple = 2 THEN fa.apple ELSE NULL END AS apple_2,
         MAX(CASE WHEN fa.apple = 3 THEN fa.apple ELSE NULL END AS apple_3,
    FROM FARM_APPLES fa
GROUP BY fa.farm

Return only one row from the right-most table for every row in the left-most table

Use:

  SELECT u.id,
         u.name,
         MIN(t.spent) AS spent
    FROM USERS u
    JOIN TRANSACTIONS t ON t.uid = u.id
GROUP BY u.id, u.name

Mind that this will only return users who have at least one TRANSACTIONS record. If you want to see users who don't have supporting records as well as those who do - use:

   SELECT u.id,
          u.name,
          COALESCE(MIN(t.spent), 0) AS spent
     FROM USERS u
LEFT JOIN TRANSACTIONS t ON t.uid = u.id
 GROUP BY u.id, u.name

Query only the first detail record for each master record

Using Sql Server 2005+ you can try (Full example)

DECLARE @owner_tbl TABLE(
        [owner] VARCHAR(50)
)

DECLARE @auto_tbl TABLE(
        [owner] VARCHAR(50),
        [auto] VARCHAR(50),
        [year]VARCHAR(4)
)

INSERT INTO @owner_tbl SELECT 'john'
INSERT INTO @owner_tbl SELECT 'james'
INSERT INTO @owner_tbl SELECT 'jeff'        

INSERT INTO @auto_tbl SELECT 'john','corvette','1968'
INSERT INTO @auto_tbl SELECT 'john','prius','2008'
INSERT INTO @auto_tbl SELECT 'james','f-150','2004'
INSERT INTO @auto_tbl SELECT 'james','cadillac','2002'
INSERT INTO @auto_tbl SELECT 'james','accord','2009'
INSERT INTO @auto_tbl SELECT 'jeff','tesla','2010'
INSERT INTO @auto_tbl SELECT 'jeff','hyundai','1996'

;WITH Autos AS(
        SELECT  *,
                ROW_NUMBER() OVER(PARTITION BY a.owner ORDER BY a.year) ROWID
        FROM    @auto_tbl a
)
SELECT  *
FROM    Autos
WHERE   ROWID = 1
ORDER BY owner

Combine master and detail table into one row by combining detail fields

Pivoting, whether it is done with the PIVOT operator (starting from Oracle version 11.1) or with conditional aggregation (version 10.2 or earlier), works equally well on any set of rows, whether that is a single table, the result of joining two tables, or the result of any other set operation permitted in SQL (another aggregation, a UNION ALL, or anything else).

You were pretty close in your attempt. All you have to do is to GROUP BY MASTERTABLE.ID (since you want only one row per MASTERTABLE.ID) and to take the MAX of all those CASE expressions: if at least one of them is 'Y' then the MAX is 'Y', otherwise it is NULL.

So:

In your query, EXACTLY AS IT IS,

• change CASE .... END as BIRD to MAX(CASE ... END) as BIRD
• add GROUP BY MASTERTABLE.ID at the end

SELECT single row from child table for each row in parent table

"get only one row from child table for each parent row with child fields included"

That sounds like the child table can have more than one row for the same pID value. And you want only one child row for each pID.

SELECT pID, Min(cID) AS MinOfcID
FROM child
GROUP BY pID;

Join that GROUP BY query back to the child table again to retrieve the other columns for each target cID value. Save this query as qryChild.

SELECT
    c.pID,
    c.cID,
    c.phone,
    c.company,
    c.title,
    c.address
FROM
    (
        SELECT pID, Min(cID) AS MinOfcID
        FROM child
        GROUP BY pID
    ) AS map
    INNER JOIN child AS c
    ON c.cID = map.MinOfcID;

Finally, to include lastname values, join the parent table to qryChild.

Selecting the first N rows of each group ordered by date

As well as the row_number solution, another option is CROSS APPLY(SELECT TOP:

SELECT m.masterid,
       d.detailid,
       m.numbers,
       d.date_time,
       d.value
    FROM masters AS m
    CROSS APPLY (
        SELECT TOP (3) *
        FROM details AS d
        WHERE d.date_time >= '2020-01-01'
        AND m.masterid = d.masterid
    ) AS d
    WHERE m.tags LIKE '%Tag2%'
    ORDER BY m.masterid DESC,
             d.date_time;

This may be faster or slower than row_number, mostly depending on cardinalities (quantity of rows) and indexing.

If indexing is good and it's a small number of rows it will usually be faster. If the inner table needs sorting or you are anyway selecting most rows then use row_number.

SQL table with a single row?

I've seen something like this when a developer was asked to create a configuration table to store name-value pairs of data that needs to persist without being changed often. He ended up creating a one-row table with a column for each configuration variable. I wouldn't say it's a good idea, but I can certainly see why the developer did it given his instructions. Needless to say it didn't pass review.

I've just observed in some code I'm reviewing three different tables that contain three different kinds of certificates (a la SSL), each having exactly one row. I don't understand why this isn't made into one row; I assume I'm missing something.

This doesn't sound like good design, unless there are some important details you don't know about. If there are three pieces of information that have the same constraints, the same use and the same structure, they should be stored in the same table, 99% of the time. That's a big part of what tables are for fundamentally.

LEFT JOIN but take only one row from right side

Use OUTER APPLY:

SELECT dok1.*, k2.*
FROM ks__dokument dok1 OUTER APPLY
     (SELECT TOP (1) *
      FROM ks_pz 
      WHERE ks_id = kp_ksid
      ) k2
WHERE ks_usuniety = 0 AND
      ks_data_otrzymania >= '2020-08-31'
ORDER BY ks_rok, ks_nr ASC;

Normally, there would be an ORDER BY in the subquery to specify which row to return.

The structure of your question makes it impossible to know if the ORDER BY should be in the subquery or in the outer query -- and the same for the WHERE conditions.

You really need to specify the tables where columns are coming from.

---

Related Topics