-1

I want to understand it on this "simple" query example. I wanto to view dates and order IDs with the highest order value (profit) in each quarter of 1997. I can write something like this.

SELECT o.OrderDate, o.OrderID, DATEPART(QUARTER, o.OrderDate)
FROM Orders o INNER JOIN (SELECT...)
WHERE YEAR(o.OrderDate) = '1997'
GROUP BY o.OrderDate, o.OrderID; 

OrderDetails contains order value (price). Catch is i don't want to view the value, so i need to use SELECT with INNER JOIN or WHILE (probably INNER JOIN in this case). I have tried multiple solutions and it shows more than 800 rows, when it should only return 4 rows. There is also a problem that GROUP BY won't really work as expected with OrderDate.

I expect results like this:

Order date | OrderID | Quarter
05.02.1997 | 173412  | 1
05.04.1997 | 421552  | 2
12.07.1997 | 735245  | 3
24.12.1997 | 825425  | 4

Query with some small explanation will be highly appreciated.

New contributor
Corporal203 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 2
    update your question add a proper data sample and the expected result as tabular text (not image) ... what do you mean with "with the highest value " ..?? which? value ?? – scaisEdge Nov 7 at 16:06
  • 2
    Why GROUP BY when no aggregate functions are involved? Why join when you only select columns from one of the tables? – jarlh Nov 7 at 16:09
  • 1
    Based on what you wrote, your query is already logically flawed. You join to OrderDetails yet you refer to none of the columns in your query (excluding the join). Use of a <group by> clause without an aggregate is also a clue that you are attempting to cover up a logic flaw. Lastly, you use the term "highest value" but you do not define what this means. So the first step is to define that and, if needed, the logic you need to compute it. Without that, there is no point to attempt to write additional logic that is based on this value. – SMor Nov 7 at 16:09
  • 1
    Highest value of what? Have a look at row_number or rank – dnoeth Nov 7 at 16:10
  • 2
    You are selecting no columns from od, so why bother with the join? – Gordon Linoff Nov 7 at 16:51
0

You probably want some kind of Windowed Aggregate:

with cte as
 (
   SELECT *,
      -- "highest value" per quarter
      max(whatever)
      over (partition by DATEPART(QUARTER, o.OrderDate)) as max_value
   FROM OrderDetails od INNER JOIN Orders o ON od.OrderID = o.OrderID
   WHERE YEAR(o.OrderDate) = '1997'
 ) 
select distinct
   o.OrderDate, o.OrderID, DATEPART(QUARTER, o.OrderDate)
from cte
-- current value = highest value of the quarter 
where whatever = max_value 

This will return more than a single row per quarter if multiple rows with the same highest value exist. If you just want a single row you must switch to row_number

with cte as
 (
   SELECT *,
      -- "highest value" per quarter = row_number 1
      row_number()
      over (partition by DATEPART(QUARTER, o.OrderDate)
            order by whatever desc) as rn
   FROM OrderDetails od INNER JOIN Orders o ON od.OrderID = o.OrderID
   WHERE YEAR(o.OrderDate) = '1997'
 ) 
select o.OrderDate, o.OrderID, DATEPART(QUARTER, o.OrderDate)
from cte
-- "highest value" per quarter = row_number 1
where rn = 1 

If you insist upon using old SQL, you can do the same without using Windowed Aggregates (but way more complex and probably way less efficient):

with cte as
 ( -- prepare the base data to avoid writing the same join/where twice
   SELECT o.OrderDate, o.OrderID, DATEPART(QUARTER, o.OrderDate) as q, whatever
   FROM OrderDetails od INNER JOIN Orders o
     ON od.OrderID = o.OrderID
   WHERE YEAR(o.OrderDate) = '1997'
 )
SELECT DISTINCT t1.OrderDate, t1.OrderID, t1.q
FROM cte
join 
 ( 
   select q, 
      -- "highest value" per quarter
      max(whatever) as max_value
   from cte
   group by q
 ) as maxperq
on cte.q = maxperq.q  -- same quarter
and cte.whatever = maxperq.whatever -- highest value of the quarter
  • Here you are using different construction. I want to solve this example using this specific method to understand the method. But i appreciate the help. – Corporal203 Nov 7 at 17:02
  • @Corporal203: I added an old-style query, but you shouldn't use it :-) – dnoeth Nov 7 at 17:17
  • @Corporal203 -- what is wrong with using this way to solve it -- this is the right way to do it -- there is no other code that makes this "simple" – Hogan Nov 7 at 17:23
  • @Hogan: My 1st query using Windowed Aggregates is much simpler than the 2nd using max+join back – dnoeth Nov 7 at 17:46
  • Ok, i suppose this is what i wanted. I will analyze it. Thanks for help. – Corporal203 Nov 7 at 20:39

Your Answer

Corporal203 is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.