How do I extract the TOP 1 from a list

Chris Michalczuk
Chris Michalczuk used Ask the Experts™
on
How do I show only the last issuenumber for a sequencenumber in a list - this is my query but I get the results as show below
I only want the results to show the 2 lines marked with *********** below

SELECT
      [ACCOUNTID]
      ,[SEQUENCENUMBER]
      ,[ISSUENUMBER]
   
  FROM temp1
  where accountid='A6UJ9A000AAQ'
  order by  [SEQUENCENUMBER],ISSUENUMBER desc

  select  SEQUENCENUMBER, issuenumber FROM temp1
  where accountid='A6UJ9A000AAQ'
  group by SEQUENCENUMBER,issuenumber

  order by  [SEQUENCENUMBER] desc ,issuenumber desc



ACCOUNTID      SEQUENCENUMBER      ISSUENUMBER
A6UJ9A000AAQ      4264      14  *********************
A6UJ9A000AAQ      4264      13
A6UJ9A000AAQ      4264      12
A6UJ9A000AAQ      4264      11
A6UJ9A000AAQ      4264      10
A6UJ9A000AAQ      4264      9
A6UJ9A000AAQ      4264      8
A6UJ9A000AAQ      21600      1 ********************
A6UJ9A000AAQ      21600      0
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
SELECT 
       [ACCOUNTID]
       ,[SEQUENCENUMBER]
       ,MAX([ISSUENUMBER])
FROM temp1
where accountid='A6UJ9A000AAQ'
group by  [ACCOUNTID], [SEQUENCENUMBER]
order by  [SEQUENCENUMBER],ISSUENUMBER desc

Open in new window

Chris MichalczukConsultant

Author

Commented:
Hi Vitor sorry but this returns all the records - I only want the first one for each sequencenumber so I am expecting to return 2 records only
ACCOUNTID      SEQUENCENUMBER      ISSUENUMBER
A6UJ9A000AAQ      4264      14  *********************
A6UJ9A000AAQ      21600      1 ********************

I am still returning


ACCOUNTID      SEQUENCENUMBER      ISSUENUMBER
A6UJ9A000AAQ      4264      14  *********************
A6UJ9A000AAQ      4264      13
A6UJ9A000AAQ      4264      12
A6UJ9A000AAQ      4264      11
A6UJ9A000AAQ      4264      10
A6UJ9A000AAQ      4264      9
A6UJ9A000AAQ      4264      8
A6UJ9A000AAQ      21600      1 ********************
A6UJ9A000AAQ      21600      0
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
It shouldn't. Can you post a print screen with my query and the results?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Chris MichalczukConsultant

Author

Commented:
was missing the brackets!!!!  order by  [SEQUENCENUMBER],MAX([ISSUENUMBER]) desc
 works now thanks


SELECT
       [ACCOUNTID]
       ,[SEQUENCENUMBER]
       ,MAX([ISSUENUMBER])
FROM [dbo].[VW_CPM_LICENSEMASTER]
where accountid='A6UJ9A000AAQ'
group by  [ACCOUNTID], [SEQUENCENUMBER]
order by  [SEQUENCENUMBER],MAX([ISSUENUMBER]) desc
IT Engineer
Distinguished Expert 2017
Commented:
Great.
You can also replace the field names by their ordered numbers in the SELECT, so in this case:
order by  2, 3 desc

If you satisfied with the solution please accept it so this question can be closed.

Cheers
awking00Information Technology Specialist

Commented:
select accountid, sequencenumber, issuenumber from
(select accountid, sequencenumber, issuenumber,
 row_number() over (partition by accountid, sequencenumber order by issuenumber desc) rn
 from temp1) x
where rn = 1;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial