How do I extract the TOP 1 from a list

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
Chris MichalczukConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT 
       [ACCOUNTID]
       ,[SEQUENCENUMBER]
       ,MAX([ISSUENUMBER])
FROM temp1
where accountid='A6UJ9A000AAQ'
group by  [ACCOUNTID], [SEQUENCENUMBER]
order by  [SEQUENCENUMBER],ISSUENUMBER desc

Open in new window

Chris MichalczukConsultantAuthor 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ãoMSSQL Senior EngineerCommented:
It shouldn't. Can you post a print screen with my query and the results?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Chris MichalczukConsultantAuthor 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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
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;
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.