Select first entries in a table matching given criteria
-
Here's a problem I've come up against on a number of occasions and I have so far failed to come up with an elegant query to deal with it. I feel the GROUP BY clause might have to come into play but I don't really know how. Let's say I have a table called
Orderscontaining information on orders received from customers. The table has the following columns:CustomerID (INT) OrderPlaced (DATETIME) ProductID (INT) AmountOrdered (INT)So I want to write a query that will show me the information from this table on the first order for a specific product (let's say id 123) received by every customer. The following query will get me almost there:SELECT CustomerID, MIN(OrderPlaced)
FROM Orders
WHERE ProductID = 123It only gets me almost there though because notice how the
AmountOrderedfield is missing from the query and unless it's part of an aggregate function I can't add it. I can add it with a subquery but that is where the query loses its elegance rapidly, especially considering that the real world tables I'm dealing with usually have more than just one or two extra columns. Any ideas? -
Here's a problem I've come up against on a number of occasions and I have so far failed to come up with an elegant query to deal with it. I feel the GROUP BY clause might have to come into play but I don't really know how. Let's say I have a table called
Orderscontaining information on orders received from customers. The table has the following columns:CustomerID (INT) OrderPlaced (DATETIME) ProductID (INT) AmountOrdered (INT)So I want to write a query that will show me the information from this table on the first order for a specific product (let's say id 123) received by every customer. The following query will get me almost there:SELECT CustomerID, MIN(OrderPlaced)
FROM Orders
WHERE ProductID = 123It only gets me almost there though because notice how the
AmountOrderedfield is missing from the query and unless it's part of an aggregate function I can't add it. I can add it with a subquery but that is where the query loses its elegance rapidly, especially considering that the real world tables I'm dealing with usually have more than just one or two extra columns. Any ideas?Almost all your solutions using an aggregate will require a sub query, there is nothing inelegant about using one! You can also look into ROW_NUMBER and PARTITION. Never underestimate the power of human stupidity RAH
-
Almost all your solutions using an aggregate will require a sub query, there is nothing inelegant about using one! You can also look into ROW_NUMBER and PARTITION. Never underestimate the power of human stupidity RAH
Thanks. I've been shying away from
Over(),ROW_NUMBERandPartition Bybecause they're rather Microsoft specific. I agree, generally there's nothing inelegant about using a subquery but in this particular case it is anything but elegant. Consider the table in my example and let's say there are a few more columns:ContractID (INT) OrderSource (INT) AgentID (INT) ProvinceID (INT) DeliveryMethod (INT)So, in order to get what I want, usingGROUP BYand subqueries, it would end up looking something like this:SELECT
CustomerID,
MIN(OrderPlaced),
--(Some or other subquery to get the value for AmountOrdered),
--(Some or other subquery to get the value for ContractID),
--(Some or other subquery to get the value for OrderSource),
--(Some or other subquery to get the value for AgentID),
--(Some or other subquery to get the value for ProvinceID),
--(Some or other subquery to get the value for DeliveryMethod)
FROM Orders
WHERE ProductID = 123
GROUP BY CustomerIDI'm sure you'll agree that the above query is pretty much the antithesis of elegance.
-
Thanks. I've been shying away from
Over(),ROW_NUMBERandPartition Bybecause they're rather Microsoft specific. I agree, generally there's nothing inelegant about using a subquery but in this particular case it is anything but elegant. Consider the table in my example and let's say there are a few more columns:ContractID (INT) OrderSource (INT) AgentID (INT) ProvinceID (INT) DeliveryMethod (INT)So, in order to get what I want, usingGROUP BYand subqueries, it would end up looking something like this:SELECT
CustomerID,
MIN(OrderPlaced),
--(Some or other subquery to get the value for AmountOrdered),
--(Some or other subquery to get the value for ContractID),
--(Some or other subquery to get the value for OrderSource),
--(Some or other subquery to get the value for AgentID),
--(Some or other subquery to get the value for ProvinceID),
--(Some or other subquery to get the value for DeliveryMethod)
FROM Orders
WHERE ProductID = 123
GROUP BY CustomerIDI'm sure you'll agree that the above query is pretty much the antithesis of elegance.
No no no you want the ID of the record for each customers earliest order (I hope) so first create a query that gets you that order ID, probably add the orderID to the above query. Make sure the IDs are the expected results before moving on to the next stage. Now wrap that query in a Select * from Orders where orderID in (Your innerquery) or if there are multiple fields in your inner query use a join Select * From Orders inner join (Yourinnerquery) X on X.orderid = orders.orderid You can make the inner query as complex as required but it should return the minimum required to join to your field table (Orders presumably)
Never underestimate the power of human stupidity RAH
-
No no no you want the ID of the record for each customers earliest order (I hope) so first create a query that gets you that order ID, probably add the orderID to the above query. Make sure the IDs are the expected results before moving on to the next stage. Now wrap that query in a Select * from Orders where orderID in (Your innerquery) or if there are multiple fields in your inner query use a join Select * From Orders inner join (Yourinnerquery) X on X.orderid = orders.orderid You can make the inner query as complex as required but it should return the minimum required to join to your field table (Orders presumably)
Never underestimate the power of human stupidity RAH
BINGO! You've hit the nail on the head. Thanks. Here is the query that does that:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT (
SELECT TOP 1 OrderID
FROM Orders O2
WHERE CustomerID = O1.CustomerID
ORDER BY Inserted
)
FROM Orders O1
GROUP BY CustomerID
)Of course this query relies on the existence of
OrderID, a primary key of theOrderstable. I didn't define it in my original question but just about every table should have a primary key anyway and just about all my tables usually do :) The great thing about this query is that the outermost query can have aSELECT *and, as you say, could even join the Orders table with any other table that might contain linked information on the orders. Thanks again. It might seem insignificant but you've just helped me solve a problem I've been grappling with for years and never really bothered to definitively put to bed. -
BINGO! You've hit the nail on the head. Thanks. Here is the query that does that:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT (
SELECT TOP 1 OrderID
FROM Orders O2
WHERE CustomerID = O1.CustomerID
ORDER BY Inserted
)
FROM Orders O1
GROUP BY CustomerID
)Of course this query relies on the existence of
OrderID, a primary key of theOrderstable. I didn't define it in my original question but just about every table should have a primary key anyway and just about all my tables usually do :) The great thing about this query is that the outermost query can have aSELECT *and, as you say, could even join the Orders table with any other table that might contain linked information on the orders. Thanks again. It might seem insignificant but you've just helped me solve a problem I've been grappling with for years and never really bothered to definitively put to bed.You are definitely welcome, once you get your head around SQL it can be very rewarding making the dammed thing do what you want. Then the bastards introduce a new way of doing something (CTEs come to mind) and you have to start all over again!
Never underestimate the power of human stupidity RAH
-
BINGO! You've hit the nail on the head. Thanks. Here is the query that does that:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT (
SELECT TOP 1 OrderID
FROM Orders O2
WHERE CustomerID = O1.CustomerID
ORDER BY Inserted
)
FROM Orders O1
GROUP BY CustomerID
)Of course this query relies on the existence of
OrderID, a primary key of theOrderstable. I didn't define it in my original question but just about every table should have a primary key anyway and just about all my tables usually do :) The great thing about this query is that the outermost query can have aSELECT *and, as you say, could even join the Orders table with any other table that might contain linked information on the orders. Thanks again. It might seem insignificant but you've just helped me solve a problem I've been grappling with for years and never really bothered to definitively put to bed.<edit>low on caffeine => dysfunctional brain</edit>
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
-
<edit>low on caffeine => dysfunctional brain</edit>
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln
Jörgen Andersson wrote:
I believe one subquery is unnecessary. Try:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT TOP 1 OrderID
FROM Orders O1
GROUP BY CustomerID
ORDER BY Inserted
)I'm not sure I understand that inner query. For one
OrderIDin theSELECTclause should surely be part of an aggregate function seeing as it is not in theGROUP BYclause. Also, forInsertedto be contained in theORDER BYclause it will also have to be part of an aggregate function. What am I missing? -
Jörgen Andersson wrote:
I believe one subquery is unnecessary. Try:
SELECT *
FROM Orders
WHERE OrderID IN (
SELECT TOP 1 OrderID
FROM Orders O1
GROUP BY CustomerID
ORDER BY Inserted
)I'm not sure I understand that inner query. For one
OrderIDin theSELECTclause should surely be part of an aggregate function seeing as it is not in theGROUP BYclause. Also, forInsertedto be contained in theORDER BYclause it will also have to be part of an aggregate function. What am I missing?You're not missing anything, it's me having had to little coffee and a non-functional brain. Sorry 'bout that.
Be excellent to each other. And... PARTY ON, DUDES! Abraham Lincoln