How to Select Multiple Columns from a Subquery (In SQL Server) That Should Have One Record (Select Top 1) for Each Record in the Main Query

Select multiple columns from a subquery in SQL Server

You can use OUTER APPLY:

SELECT t1.ID, t1.fck, t1.f1, t3.f2, t3.f3
FROM tbl1 AS t1
OUTER APPLY (
   SELECT f2, f3 
   FROM tbl2 AS t2 
   WHERE t2.ID = t1.fck) AS t3

The SELECT clause contains scalar values, so it can't handle subqueries returning more than field, or multiple records of one field.

SQLServer get top 1 row from subquery

You are going down the path of using outer apply, so let's continue:

SELECT P.ID, P.Description, P... blah, blah
FROM Products p OUTER APPLY
     (SELECT TOP 1 B.Product,B.Date,B.Price --Can't take out TOP 1 if ORDER BY
      FROM Buys b
    --WHERE... Can't select by P.Product because doesn't exist in that context
      WHERE b.Product = P.ID
      ORDER BY B.Date DESC, B.ID DESC
     ) buy
WHERE (Some product family and kind restrictions, etc, so it processes a big amount of products)

In this context, you can thing of apply as being a correlated subquery that can return multiple columns. In other databases, this is called a "lateral join".

Is it possible to get multiple values from a subquery?

A Subquery in the Select clause, as in your case, is also known as a Scalar Subquery, which means that it's a form of expression. Meaning that it can only return one value.

I'm afraid you can't return multiple columns from a single Scalar Subquery, no.

Here's more about Oracle Scalar Subqueries:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm#i1033549

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

• maintain status in 2 places
• preserve status history
• ...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.

The claim that subqueries can only return single columns

In the answer that you link to, I would classify that as an "inline view" or "inline query", rather than a subquery.

That raises the question of what exactly a subquery is, of course.

Here's an example where you can indeed only return a single column.

select (select name from table where id = main_query.id),
       id
from   table main_query

Here's an example where you can return multiple columns. This seems to be to be unequivocally a subquery, of the "correlated" type.

select id
from   table main_query
where  (col1, col2) in (select a,b
                        from   c
                        where  c.x = main_query.y);

Here's an example where it doesn't matter how many columns are returned, and in fact any values are ignored:

select id
from   table main_query
where  exists (select a,b
               from   c
               where  c.x = main_query.y);

I think on balance I'd say that it is not true, but it depends on what your definition of a subquery is.

mysql: get two values from subquery

You can solve this with a JOIN, but you need to be careful to only JOIN to the oldest values for a given customer:

SELECT c.email_address, o.order_id, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id AND
    o.order_date = (SELECT MIN(order_date) FROM orders o2 WHERE o2.customer_id = c.customer_id)

Select multiple columns from a table, but group by one

I use this trick to group by one column when I have a multiple columns selection:

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse
GROUP BY Nume
ORDER BY Nume

This works.

---

Related Topics