SQL to get the third highest ID from the table with a particular type of column value

Question

I have a table say "Packages".

packageid   FlightType
    230         Common
    231          B717
    232          A330
    233          B717
    234         Common
    235          B767
    236          A330
    237          A330
    238          A330
    239          A330
    240          B767
    241          B767
    242          B767

Using SELECT Max(PackageID) AS PackageID, FlightType FROM Packages GROUP BY FlightType, I am able to get the maximum packageid for each FlightType as below

PackageID   FlightType
239         A330
233         B717
242         B767
234        Common

But my requirement is a SQL that would return third highest Packageid for each flightType. Here common is used in all the flight types. So It should be counted for each flight type, while returning the value. e.g. for FlightTYpe B717 maximum PackageID will be 234 and hence the 3rd largest will be 231.Over all for given table the output of the query should be

PackageID   FlightType
237         A330
231         B717
240         B767

Note:-I don't require the output to have the 3rd highest packageid for 'common'. because It denotes the package which is common to all the FlightTypes.

The expected resul is inconsistent with the description. It excludes Common without any reason and it choses 230 for B717 that is not third but the second. You should describe why do you want the result to be as it's posted — sepupic, Dec 15 '17 at 09:51
@sepupic Its even worse, there is no packageid 230 for B717. I think he meant 231 — GuidoG, Dec 15 '17 at 10:12
Sorry, I wanted to say B767 has 240 as the SECOND packageid (so why don't we get 241 that is the THIRD), and B717 has NO THIRD at all, so the result is very strange, should we take the second if there is no third? — sepupic, Dec 15 '17 at 10:22
@sepupic I think that is indeed what he means, I based my answer upon that assumption — GuidoG, Dec 15 '17 at 10:29
@sepupic@GuidoG@Sami ,I'm extremely sorry for the late reply.and yeah, B717 should return PackageId 231.Here the main concern is that 'Common' is used for the packageId which is common to all the FlightTypes. So it should be taken into account for all the FlightTypes. Please see my updated question. — rkm, Dec 16 '17 at 05:37

Jayasurya Satheesh · Answer 1 · 2017-12-16T06:06:26.633

1

Use this

For Filter for each FlightType

WITH CTE
AS
(
    SELECT
        SeqNo = ROW_NUMBER() OVER(PARTITION BY FlightType ORDER BY PackageId DESC),
        *
        FROM Packages
)
SELECT
    *
    FROM CTE
        WHERE SeqNo = 3

Filter for each PackageType

WITH CTE
    AS
    (
        SELECT
            SeqNo = ROW_NUMBER() OVER(PARTITION BY PackageId ORDER BY FlightType DESC),
            *
            FROM Packages
    )
    SELECT
        *
        FROM CTE
            WHERE SeqNo = 3

Replace 3 with any number to get the corresponding record for each package ex : 1 - to get the largest, 2 for 2nd largest etc

edited Dec 16 '17 at 06:06

answered Dec 15 '17 at 09:39

Jayasurya Satheesh

7,826
3
22
39

What about `230`?? – Ilyes Dec 15 '17 at 09:41
For each FlightType you will get the 3rd largest – Jayasurya Satheesh Dec 15 '17 at 09:42
yes, but in his expected outcome there is also 230 which has no 3th largest but only a second largest – GuidoG Dec 15 '17 at 09:43
but he says 3rd largest in his requrment – Jayasurya Satheesh Dec 15 '17 at 09:46
1

your answer is absolute correct when you read his description, but not when you look at his expected outcome. The OP should clear this up. – GuidoG Dec 15 '17 at 09:54
@JayasuryaSatheesh It considers 'Common' as an individual FlightType.But 'Common' here is for the 'Packageid' which is common to all the flight types. Please see the updated question. sorry for late response! – rkm Dec 16 '17 at 06:02

score 1 · Answer 2 · answered Dec 15 '17 at 10:46

1

If the intended outcome is the max position until 3rd place:

with cte as ( 
    select    packageid
            , flighttype
            , ROW_NUMBER() OVER(Partition by flighttype order by packageid desc) as pos
    from #Packages)
select *
from cte c1
where pos = (select max(c2.pos) from cte c2 where pos<=3 and c1.FlightType = c2.FlightType)

answered Dec 15 '17 at 10:46

cloudsafe

2,444
1
8
24

Well done. It gets the same result as my answer, but I think this will perform better than my way – GuidoG Dec 15 '17 at 10:48
@cloudsafe, Sorry for the late respone. this query returns the 3rd largest by considering 'common' also as an individual flightType. But 'Common' here denotes the Pckageid which is common to all the flightTypes. Please have a look at my updated question. – rkm Dec 16 '17 at 05:56

GuidoG · Answer 3 · 2017-12-15T12:48:33.037

I think in your expected outcome there is a mistake for B717, this should be 231 not 230
If that is correct you can try this :

First lets create a in memory table where we can test on

declare @Packages table (packageid int, flighttype varchar(10))
insert into @Packages (packageid, flighttype)
values (230, 'Common'), (231, 'B717'), (232, 'A330'), (233, 'B717'), (234, 'Common'), (235, 'B767'),
       (236, 'A330'), (237, 'A330'), (238, 'A330'), (239, 'A330'), (240, 'B767'), (241, 'B767'), (242, 'B767')

Now for your query :

select distinct p.flighttype,
       (select top 1 p3.packageid from
         ( select top 3 p2.packageid from @packages p2 where p2.flighttype = p.flighttype order by p2.packageid desc ) p3
         order by p3.packageid
       )  
from   @packages p

the outcome is this :

flighttype  packageid   
----------  ---------   
A330        237 
B717        231 
B767        240 
Common      230

This will get all 3th largest packageid, and if there are only 2 then it will get the second, if there is only one it will get the first

Thanks for your response.Your outcome is as expected, But approach is different. B717 should return PackageId 231. But not because only two packageid are there for this, Rather because maximum packageid for it(B717) is 234,which is common to all the flights. Please have a look at my updated question. — rkm, Dec 16 '17 at 05:03

SQL to get the third highest ID from the table with a particular type of column value

3 Answers3