[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

need help to construct a SQL Query to show top 3 results as one output

Hi,
I have a table name InvoiceNotes, I am looking to get 3 most recent notes given an invoice number, however, I am looking for the result on a single row. Is that possible? I am running it on MSSQL Server 2008 R2
Thanks!
Ho-Ching

InvoiceNumber  createuser        notes                createdate
1011                     John                   testnote1         2014-7-1-1:00PM
1011                 Steve                  testnote2         2014-7-1-2:00PM
1011                 Brenda               testnote3         2014-7-4-2:00PM
1011                 Megan               testnote4         2014-7-5-2:00PM
1012                 Megan               testnote5        2014-7-5-2:00PM


SELECT TOP 3  notes FROM InvoiceNotes WHERE InvoiceNumber  = 1011 ORDER BY createdate DESC

but I need the result to be one row such as:
             
testnote4@testnote3@testnote2
0
rskepton
Asked:
rskepton
3 Solutions
 
Brian CroweCommented:
Do you want the notes in separate columns or a single column?
0
 
Pooja Katiyar VermaCommented:
In MySQL there is a function,
GROUP_CONCAT() function is used to concatenate column values into a single string. It is very useful if you would otherwise perform a lookup of many row and then concatenate them on the client end.
Example:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a;
OR
 SELECT GROUP_CONCAT(Language) As Languages FROM CountryLanguage WHERE CountryCode = 'THA';

Similarly:
SELECT  InvoiceNumber  , Group_concat(notes) FROM InvoiceNotes group by InvoiceNumber  ;
0
 
PortletPaulCommented:
I interpret this to mean one row and one column (with @ as the delimiter)

This is for Microsoft SQL Server
select
        inv.InvoiceNumber
      , ca1.top3invnotes
from invoices inv
cross apply (
              select
                    STUFF(
                      ( Select top 3 '@' + notes
                        from InvNotes
                        where InvNotes.InvoiceNumber = inv.InvoiceNumber
                        order by createdate DESC
                        FOR XML PATH('')
                      ),1,1,'')
              ) as ca1 (top3invnotes)

Open in new window

This produces:
| INVOICENUMBER |                  TOP3INVNOTES |
|---------------|-------------------------------|
|          1011 | testnote4@testnote3@testnote2 |
|          1012 |                     testnote5 |
|          1013 |                        (null) |



CREATE TABLE InvNotes
	([InvoiceNumber] int, [createuser] varchar(6), [notes] varchar(9), [createdate] varchar(15))
;
	
INSERT INTO InvNotes
	([InvoiceNumber], [createuser], [notes], [createdate])
VALUES
	(1011, 'John', 'testnote1', '2014-7-1-1:00PM'),
	(1011, 'Steve', 'testnote2', '2014-7-1-2:00PM'),
	(1011, 'Brenda', 'testnote3', '2014-7-4-2:00PM'),
	(1011, 'Megan', 'testnote4', '2014-7-5-2:00PM'),
	(1012, 'Megan', 'testnote5', '2014-7-5-2:00PM')
;

CREATE TABLE Invoices
	([InvoiceNumber] int)
;
	
INSERT INTO Invoices
	([InvoiceNumber])
VALUES
	(1011),
	(1012),
	(1013)
;

http://sqlfiddle.com/#!3/f42c4/2

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rskeptonAuthor Commented:
Thanks guys!
PortletPaul, your solution works.
If it is not too much to ask, is there anyway to have the output to separate columns? That was my originally goal but I didn't know it may be possible.

Topnote1     Topnote2     Topnote3
testnote4      testnote3     testnote2
0
 
rskeptonAuthor Commented:
I should have been more clear on my question, my ultimate goal is to have the output looks like

Topnote1     Topnote2     Topnote3     TopCreatuser1   TopCreatuser2   TopCreatuser3
 testnote4      testnote3     testnote2    Megan                 Brenda                 Steve
0
 
PortletPaulCommented:
ah! the importance of "expected result" :)

try this:
select
       InvoiceNumber
     , max(case when rn = 1 then notes end) as note1
     , max(case when rn = 2 then notes end) as note2
     , max(case when rn = 3 then notes end) as note3
     , max(case when rn = 1 then createuser end) as createuser1
     , max(case when rn = 2 then createuser end) as createuser2
     , max(case when rn = 3 then createuser end) as createuser3
from (
      
      select
              InvoiceNumber
            , createuser
            , notes
            , row_number() over(partition by InvoiceNumber
                                order by createdate DESC) as rn
      from InvNotes
     ) rns
where rn in (1,2,3)
group by
        InvoiceNumber
;

| INVOICENUMBER |     NOTE1 |     NOTE2 |     NOTE3 | CREATEUSER1 | CREATEUSER2 | CREATEUSER3 |
|---------------|-----------|-----------|-----------|-------------|-------------|-------------|
|          1011 | testnote4 | testnote3 | testnote2 |       Megan |      Brenda |       Steve |
|          1012 | testnote5 |    (null) |    (null) |       Megan |      (null) |      (null) |

http://sqlfiddle.com/#!3/f42c4/7

Open in new window

0
 
rskeptonAuthor Commented:
thanks guys!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now