asked on 1/18/2016

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

Microsoft SQL Server

Last Comment

awking00

1/18/2016

Vitor Montalvão

1/18/2016

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

Open in new window

Chris Michalczuk

1/18/2016

ASKER

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ão

1/18/2016

It shouldn't. Can you post a print screen with my query and the results?

Chris Michalczuk

1/18/2016

ASKER

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

1/18/2016

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

awking00

1/18/2016

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;