Hi experts.
I need to Select the Highest $Amount (plus the Description) and the Second Highest $Amount (and that related Description) from Table1.
How can this be coded, please?
Thank you.
SELECT TOP 2 Description, $Amount
FROM Table1
ORDER BY $Amount DESC;
OR you can use the ROWNUMBER() function. If you provide usable sample data we can help you with that.
1 Like
Thanks for taking on the challenge, @RogierPronk
This is my (overly-complex) query:
SELECT dbo.FreightBills.Id AS FreightBillID, dbo.FreightBills.ShipmentId, MAX(dbo.FreightBillEdiLines.FreightRate) AS MaxAccRate, dbo.FreightBillEdiLines.Description AS MaxAccRateName, MAX(FreightBillEdiLines_1.FreightRate)
AS SecondMaxAccRate, FreightBillEdiLines_1.Description AS SecondMaxAccName
FROM dbo.FreightBillEdiLines INNER JOIN
dbo.FreightBills ON dbo.FreightBillEdiLines.FreightBillId = dbo.FreightBills.Id LEFT OUTER JOIN
dbo.FreightBillEdiLines AS FreightBillEdiLines_1 ON dbo.FreightBills.Id = FreightBillEdiLines_1.FreightBillId
WHERE (FreightBillEdiLines_1.FreightRate <
(SELECT MAX(FreightRate) AS Expr1
FROM dbo.FreightBillEdiLines AS FreightBillEdiLines_2)) AND (dbo.FreightBills.Status <> 6) AND (FreightBillEdiLines_1.EdiLineType = 3) AND (dbo.FreightBillEdiLines.EdiLineType = 3)
GROUP BY dbo.FreightBills.ShipmentId, dbo.FreightBills.Id, FreightBillEdiLines_1.Description, dbo.FreightBillEdiLines.Description
HAVING (MAX(dbo.FreightBillEdiLines.FreightRate) > 0)
It currently returns
But it should return only 1 row for a combination of FreightBillID + ShipmentID
I'm trying to select the Highest $Rate and the second -highest $Rate as well as their related descriptions. Hope this makes sense and thanks again for your time!
If you want to totals to sum by FreightBillID and ShipmentID - then only include those 2 columns in the group by. Adding in the other columns tells SQL Server that you want to sum at those levels.
You have several problems with your code:
- You need to alias your tables - it makes it much easier to read and code.
- You are using an OUTER JOIN - but including a column from that outer join in your where clause. That effectively makes it an inner join as it eliminates the NULL values.
- It appears you want to sum only the rows with the maximum (highest) FreightRate. For this is would be much simpler to use row_number to identify that row.
Something along the lines of:
With freightDetails
As (
Select ...
, rn = row_number() over(Partition By fb.Id, fb.ShipmentId Order By el.FreightRate desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3
)
Select *
From freightDetails
Where rn = 1
Now - if you want 2 rows returned, change the last where to 'Where rn <= 2'
1 Like
Thanks @jeffw8713 I only need 1 row returned for each grouping of FreightBillID + ShipmentID
and we are not totalling. Just selecting the highest $amount and second highest $amount.
@jeffw8713 I may need 2 rows returned per grouping but the second rows data needs to be on the original row in the resultset.
With freightDetails
As (
Select FB.ID, FB.ShipmentID, EL.FreightRate, EL.Description
, rn = row_number() over(Partition By fb.Id, fb.ShipmentId Order By el.FreightRate desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3
)
Select *
From freightDetails
Where rn =1 and FreightDetails.FreightRate > 0
--Where rn IN(1,2) and FreightDetails.FreightRate > 0
We are getting closer, but I need the Second Highest Rate and the Description for it included on the same row. Thanks
@jeffw8713 This is what I have now:
With freightDetails
As (
Select FB.ID, FB.ShipmentID, EL.FreightRate, EL.Description
, rn = row_number() over(Partition By fb.Id, fb.ShipmentId Order By el.FreightRate desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3
)
Select *
From freightDetails
Where rn =1 and FreightDetails.FreightRate > 0
But I also need to select the 2nd highest FreightRate and its Description - and have those columns returned in the same row as the highest rate. I've seen that trick done before but I can't remember how it was done.
Any ideas?
Thanks
Select rn <= 2 then cross tab (pivot) the data. I don't have access to a computer right now, but use something like
, MAX( CASE WHEN rn = 1 THEN col1 END)
, MAX( CASE WHEN rn = 1 THEN col2 END)
, MAX( CASE WHEN rn = 2 THEN col1 END)
, MAX( CASE WHEN rn = 2 THEN col2 END)
1 Like
@jeffw8713 That gets me a bit closer.
When I run this code
With freightDetails
As (
Select FB.ID, FB.ShipmentID, EL.FreightRate, EL.Description
, rn = row_number() over(Partition By fb.Id, fb.ShipmentId Order By el.FreightRate desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3
)
SELECT
MAX( CASE WHEN rn = 1 THEN freightDetails.ID END)
, MAX( CASE WHEN rn = 1 THEN freightDetails.ShipmentId End)
, MAX( CASE WHEN rn = 1 THEN freightDetails.FreightRate End)
, MAX( CASE WHEN rn = 1 THEN freightDetails.Description END)
, MAX( CASE WHEN rn = 2 THEN freightDetails.FreightRate End)
, MAX( CASE WHEN rn = 2 THEN freightDetails.Description END)
from freightDetails
It only returns 1 row. the largest rate $amount in the resultset. But I need all rows.
So when I remove "MAX":
With freightDetails
As (
Select FB.ID, FB.ShipmentID, EL.FreightRate, EL.Description
, rn = row_number() over(Partition By fb.Id, fb.ShipmentId Order By el.FreightRate desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3 And el.FreightRate > 0
)
SELECT
( CASE WHEN rn = 1 THEN freightDetails.ID END) as ID
, ( CASE WHEN rn = 1 THEN freightDetails.ShipmentId End) as ShipmentID
, ( CASE WHEN rn = 1 THEN freightDetails.FreightRate End) as HighestRate
, ( CASE WHEN rn = 1 THEN freightDetails.Description END) as DescForHighestRate
, ( CASE WHEN rn = 2 THEN freightDetails.FreightRate End) as SecondHighestrate
, ( CASE WHEN rn = 2 THEN freightDetails.Description END) as DescForSecondHighestRate
from freightDetails
It returns thousands of rows (which is correct) but the data I need is split across 2 rows like this:
The $11.00 and California Compliance Charge should be on the first row.
With your tip, I'm closer but still can't get the data returned on a single row for each grouping of ID + ShipmentID
Thanks
You need to add a group by. Include the id snd shipmentid without aggregates and include those rows in the group by
1 Like
Also make sure you filter by rn <= 2
1 Like
@jeffw8713 I appreciate your suggestions. I'm struggling with the first one. "You need to add a group by. Include the id snd shipmentid without aggregates and include those rows in the group by"
With freightDetails
As (
Select FB.ID, FB.ShipmentID, EL.FreightRate, EL.Description
, rn = row_number() over(Partition By fb.Id, fb.ShipmentId Order By el.FreightRate desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3 And el.FreightRate > 0
Group by FB.Id, FB.ShipmentId
)
SELECT
( CASE WHEN rn = 1 THEN freightDetails.ID END) as ID
, ( CASE WHEN rn = 1 THEN freightDetails.ShipmentId End) as ShipmentID
, ( CASE WHEN rn = 1 THEN freightDetails.FreightRate End) as HighestRate
, ( CASE WHEN rn = 1 THEN freightDetails.Description END) as DescForHighestRate
, ( CASE WHEN rn = 2 THEN freightDetails.FreightRate End) as SecondHighestRate
, ( CASE WHEN rn = 2 THEN freightDetails.Description END) as DescForSecondHighestRate
from freightDetails where rn <= 2
After adding Group by FB.Id, FB.ShipmentId
I get "column 'dbo.FreightBillEdiLines.FreightRate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."
Your tip said add the Group By without aggregates but I'm not sure how to do that.
Thanks.
The GROUP BY needs to go on the outer query - what you are doing is getting the detail data (freightDetails), assigning a row number in descending order in the 'inner' CTE query.
From that - you want to limit the results to the top 2 results per group. Because the row number function is partitioned by Id, ShipmentID - and a descending order based on the FreightRate then the highest value will be row 1 - the next highest value will be row 2, etc.
In the outer query - filtering by rn <= 2 gets us only those 2 rows, but that is 2 separate rows. From that you want to cross-tab the results into a single row per ID, ShipmentID - which requires grouping the data by those values.
What you did in your attempt is to try to get the MAX value of ID and the MAX value of ShipmentID. But those columns are what you are grouping the data on...
With freightDetails
As (
Select fb.ID
, fb.ShipmentID
, el.FreightRate
, el.Description
, rn = row_number() Over (Partition By fb.Id, fb.ShipmentId Order By el.FreightRate Desc)
From dbo.FreightBills fb
Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id
Where fb.Status <> 6
And el.EdiLinetype = 3
And el.FreightRate > 0
)
Select ID
, ShipmentID
, HighestRate = max(Case When freightDetails.rn = 1 Then freightDetails.FreightRate End))
, DescForHighestRate = max(Case When freightDetails.rn = 1 Then freightDetails.Description End))
, SecondHighestRate = max(Case When freightDetails.rn = 2 Then freightDetails.FreightRate End))
, DescForSecondHighestRate = max(Case When freightDetails.rn = 2 Then freightDetails.Description End))
From freightDetails
Where freightDetails.rn <= 2
Group By
ID
, ShipmentID;
So - we need to include those columns in the outer query (without aggregates) - and include them in the GROUP BY so we can rollup the values to each ID, ShipmentID.
1 Like
Thanks very much for your patience in explaining your solution, @jeffw8713.
After cleaning up the unbalanced brackets, this is the final version:
With freightDetails As ( Select fb.ID , fb.ShipmentID ,
el.FreightRate , el.Description , rn = row_number() Over (Partition By fb.Id, fb.ShipmentId Order By el.FreightRate Desc)
From dbo.FreightBills fb Inner Join dbo.FreightBillEdiLines el On el.FreightBillId = fb.Id Where fb.Status <> 6 And el.EdiLinetype = 3
And el.FreightRate > 0 )
Select ID as FreightBillID, ShipmentID , HighestRate = max(Case When freightDetails.rn = 1 Then freightDetails.FreightRate End),
DescForHighestRate = max(Case When freightDetails.rn = 1 Then freightDetails.Description End) , SecondHighestRate = max(Case When freightDetails.rn = 2
Then freightDetails.FreightRate End), DescForSecondHighestRate = max(Case When freightDetails.rn = 2 Then freightDetails.Description End)
From freightDetails Where freightDetails.rn <= 2 Group By ID , ShipmentID;
And it's fast. Returns a 6,000 row resultset in 1 second.
Thanks again, Your help was invaluable.