Solved

Group by and order by clause

Posted on 2016-11-18
28
46 Views
Last Modified: 2016-11-19
hello,

how to group by col1 and order by col2 in query

Select * from table groupby col1 and order by col2

Cheers
0
Comment
Question by:RIAS
  • 15
  • 6
  • 5
  • +2
28 Comments
 

Author Comment

by:RIAS
ID: 41893018
Example:

table1

Col1                           Col2

Number1                  
Number2
Number1
Number2               1
Number3               1


Expected output


Col1                           Col2

Number1                  
Number1
Number2
Number2               1
Number3               1

Cheers
0
 

Author Comment

by:RIAS
ID: 41893021
My col2 is integer. .Arrange it that null remains at top
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41893026
SELECT col1
GROUP BY col1
FROM table
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END

or

directly


SELECT col1
GROUP BY col1
FROM table
ORDER BY col2
0
 

Author Comment

by:RIAS
ID: 41893039
Pawan
,
Syntax error near group by
0
 

Author Comment

by:RIAS
ID: 41893040
Can you please  give an example of working query
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41893042
SELECT col1
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
0
 

Author Comment

by:RIAS
ID: 41893049
Nope,
JHere is the query

    SET @SQL = 'SELECT col1,col2
				FROM [PT] 	
				WHERE ( DeleteRecord is null or DeleteRecord = 0)'    	 
		
	   IF  @Description1  <> ''
            SET @SQL = @SQL + '  AND Description1 LIKE  ''%' + CAST(@Description1 AS  Nvarchar(MAX)) +  '%'''  			
	   SET @SQL = @SQL  + 'ORDER BY Checked asc'	

       EXECUTE (@SQL)

Open in new window

0
 

Author Comment

by:RIAS
ID: 41893050
Pawan,
Can you help in my query
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41893061
Try..

      DECLARE @SQL AS VARCHAR(1000) = ''
	  DECLARE @Description1 AS VARCHAR(1000) = ''

	  SET @SQL = 'SELECT col1,col2
				FROM [PT] 	
				WHERE ( DeleteRecord is null or DeleteRecord = 0)'    	 
		
	   IF  @Description1  <> ''
            SET @SQL = @SQL + '  AND Description1 LIKE  ''%' + CAST(@Description1 AS  Nvarchar(MAX)) +  '%'''  			
	   SET @SQL = @SQL  + ' ORDER BY CASE WHEN Checked IS NULL OR '''' THEN 0 ELSE Checked END ASC '	

       EXEC (@SQL)

Open in new window

0
 

Author Comment

by:RIAS
ID: 41893064
Pawan,
Where is groupby?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41893066
RIAS, I can't see any aggregate function nor the GROUP BY statement in your code.
0
 

Author Comment

by:RIAS
ID: 41893068
Vitor,

There is where I want help.
The query is my original query .
i need to have this output

Example:

table1

Col1                           Col2

Number1                  
Number2
Number1
Number2               1
Number3               1


Expected output


Col1                           Col2

Number1                  
Number1
Number2
Number2               1
Number3               1

Cheers
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41893070
SELECT col1, MAX(Col2) col2
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41893079
If you don't have any aggregate function then the follow should work.
Select co1, col2
from table 
group by col1, col2
order by col2

Open in new window

Otherwise you can't do it with a simple select because it will return the following error:
Column "col2" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:RIAS
ID: 41893082
Vitor,
Gave an error as it says need an aggregate function.
Can you advise
0
 

Author Comment

by:RIAS
ID: 41893085
my query
   DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT [InvoiceDate],SupplierTitle,ClientCode,ProactisCode,Currency,GrossValue,Description,Description1,Description2,Comments,
	            GuestDetails,InvoiceNumber,InvoiceGUID,Checkedby,Note,TravelExpenseType,Accomodation,FoodBeverage,Hotel,Laundry,Miscellaneous,Parking,Phone,
				Originator,Checked,Position
				FROM [PROACTISINVOICEEXTRACT] 	
				WHERE ( DeleteRecord is null or DeleteRecord = 0)'   
	
	 	 
				 
		IF @InvoiceDate1  <> '' AND @InvoiceDate2  <> ''
		     SET @SQL =@SQL+ '   AND [InvoiceDate] >= ' + ''''+ CAST(@InvoiceDate1  AS VARCHAR(20))  + '''' + ' and [InvoiceDate] <= ' + ''''+ CAST(@InvoiceDate2  AS VARCHAR(20)) +''''		  
	    ELSE IF  @InvoiceDate1  <> ''
			 SET @SQL = @SQL+ '   AND  [InvoiceDate] >= '+ '''' + CAST(@InvoiceDate1  AS VARCHAR(20))  +''''	
		Else IF @InvoiceDate2  <> ''		
		     SET @SQL = @SQL+ '  AND  [InvoiceDate] <= '+'''' + CAST(@InvoiceDate2  AS VARCHAR(20))+''''			 	 
       IF  @SupplierTitle  <> ''
	         SET @SQL = @SQL + '  AND [SupplierTitle]   LIKE  ''%' +  CAST(@SupplierTitle AS VARCHAR(100))  +  '%'''      
       IF  @ClientCode   <> ''
              SET @SQL = @SQL + '  AND ClientCode = ''' +  CAST(@ClientCode AS NVARCHAR(10))+ ''''
	   IF  @Checked <> '0'  or @Checked   <> null
             SET @SQL = @SQL + '  AND Checked = ''' +  CAST(@Checked AS NVARCHAR(10))+ ''''
       IF  @Currency <> ''
            SET @SQL = @SQL + '  AND Currency = ''' +  CAST(@Currency AS NVARCHAR(12))+ '''' 
       IF  @GrossValue <> ''
            SET @SQL = @SQL + '  AND GrossValue = ''' +  CAST(@GrossValue AS NVARCHAR(255))+ ''''
	   IF  @Description1  <> ''
            SET @SQL = @SQL + '  AND Description1 LIKE  ''%' + CAST(@Description1 AS  Nvarchar(MAX)) +  '%'''  
	   IF  @Description2  <> ''
            SET @SQL = @SQL + '  AND Description2 LIKE  ''%' + CAST(@Description2 AS  Nvarchar(MAX)) +  '%'''  	  
	   IF  @Comments <> ''
             SET @SQL = @SQL + '  AND Comments LIKE  ''%' + CAST(@Comments AS  Nvarchar(MAX)) +  '%''' 	 	 
		IF  @GuestDetails <> ''
             SET @SQL = @SQL + '  AND GuestDetails LIKE  ''%' + CAST(@GuestDetails AS  Nvarchar(MAX)) +  '%'''   	 

	 	IF  @InvoiceNumber <> ''
             SET @SQL = @SQL + '  AND InvoiceNumber = ''' +   CAST(@InvoiceNumber AS  Nvarchar(255))+ ''''
	 	IF  @CheckedBy <> ''
            SET @SQL = @SQL + '  AND CheckedBy = ''' +  CAST(@CheckedBy AS NVARCHAR(100))+ ''''
	
		
	  IF  @Note   <> ''
             SET @SQL = @SQL + '  AND Note = ''' +  CAST(@Note AS NVARCHAR(50))+ ''''
  
	

	  




----	A period of time --> WHERE date >= @date1 and date <= @date2 (you will to receive two parameters for the date period (min and max dates).

----Before than ---> WHERE date < @date
----Later than --> WHERE date > @date

			
	   SET @SQL = @SQL  + 'GROUP BY INVOICENUMBER,CHECKED ORDER BY CHECKED'

       EXECUTE (@SQL)
	   INSERT INTO TRAVEL_DEBUG(SQL) VALUES (@SQL)

Open in new window

0
 

Author Comment

by:RIAS
ID: 41893087
Checked is the int column and invoice is for group by
0
 

Author Comment

by:RIAS
ID: 41893088
Sorry invoice number
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41893095
You're only GROUP BY INVOICENUMBER,CHECKED when you have plenty of fields in the SELECT column list.
What for you need the GROUP BY clause? Leaving only the ORDER BY clause won't give what you need?
0
 

Author Comment

by:RIAS
ID: 41893100
groupby was a guess.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 41893105
groupby was a guess.
Lady, you can't guess like that :)
Tell us what you're trying to do. Add samples and we should be able to provide you a solution for your requirement.
Btw, did you try to see if ORDER BY alone works for you?
SET @SQL = @SQL  + ' ORDER BY CHECKED'

Open in new window

0
 

Author Comment

by:RIAS
ID: 41893110
Sorry :<

order by checked is fine. But how to group the invoicenumber together


InvNumber                           Checked

Number1                  
Number1
Number2
Number2                                
Number3                                  1
0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41893119
For now forget about the GROUP BY.
What about ordering by the 2 columns?
SET @SQL = @SQL  + ' ORDER BY INVOICENUMBER, CHECKED'

Open in new window

0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41893133
another guess..

   DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT [InvoiceDate],MAX(SupplierTitle) SupplierTitle,MAX(ClientCode)ClientCode,MAX(ProactisCode)ProactisCode,MAX(Currency)Currency
				,Checked
				FROM [PROACTISINVOICEEXTRACT] 	
				WHERE ( DeleteRecord is null or DeleteRecord = 0)'   
	
	 	 
				 
		IF @InvoiceDate1  <> '' AND @InvoiceDate2  <> ''
		     SET @SQL =@SQL+ '   AND [InvoiceDate] >= ' + ''''+ CAST(@InvoiceDate1  AS VARCHAR(20))  + '''' + ' and [InvoiceDate] <= ' + ''''+ CAST(@InvoiceDate2  AS VARCHAR(20)) +''''		  
	    ELSE IF  @InvoiceDate1  <> ''
			 SET @SQL = @SQL+ '   AND  [InvoiceDate] >= '+ '''' + CAST(@InvoiceDate1  AS VARCHAR(20))  +''''	
		Else IF @InvoiceDate2  <> ''		
		     SET @SQL = @SQL+ '  AND  [InvoiceDate] <= '+'''' + CAST(@InvoiceDate2  AS VARCHAR(20))+''''			 	 
       IF  @SupplierTitle  <> ''
	         SET @SQL = @SQL + '  AND [SupplierTitle]   LIKE  ''%' +  CAST(@SupplierTitle AS VARCHAR(100))  +  '%'''      
       IF  @ClientCode   <> ''
              SET @SQL = @SQL + '  AND ClientCode = ''' +  CAST(@ClientCode AS NVARCHAR(10))+ ''''
	   IF  @Checked <> '0'  or @Checked   <> null
             SET @SQL = @SQL + '  AND Checked = ''' +  CAST(@Checked AS NVARCHAR(10))+ ''''
       IF  @Currency <> ''
            SET @SQL = @SQL + '  AND Currency = ''' +  CAST(@Currency AS NVARCHAR(12))+ '''' 
       IF  @GrossValue <> ''
            SET @SQL = @SQL + '  AND GrossValue = ''' +  CAST(@GrossValue AS NVARCHAR(255))+ ''''
	   IF  @Description1  <> ''
            SET @SQL = @SQL + '  AND Description1 LIKE  ''%' + CAST(@Description1 AS  Nvarchar(MAX)) +  '%'''  
	   IF  @Description2  <> ''
            SET @SQL = @SQL + '  AND Description2 LIKE  ''%' + CAST(@Description2 AS  Nvarchar(MAX)) +  '%'''  	  
	   IF  @Comments <> ''
             SET @SQL = @SQL + '  AND Comments LIKE  ''%' + CAST(@Comments AS  Nvarchar(MAX)) +  '%''' 	 	 
		IF  @GuestDetails <> ''
             SET @SQL = @SQL + '  AND GuestDetails LIKE  ''%' + CAST(@GuestDetails AS  Nvarchar(MAX)) +  '%'''   	 

	 	IF  @InvoiceNumber <> ''
             SET @SQL = @SQL + '  AND InvoiceNumber = ''' +   CAST(@InvoiceNumber AS  Nvarchar(255))+ ''''
	 	IF  @CheckedBy <> ''
            SET @SQL = @SQL + '  AND CheckedBy = ''' +  CAST(@CheckedBy AS NVARCHAR(100))+ ''''
	
		
	  IF  @Note   <> ''
             SET @SQL = @SQL + '  AND Note = ''' +  CAST(@Note AS NVARCHAR(50))+ ''''
  
			
	   SET @SQL = @SQL  + 'GROUP BY INVOICENUMBER,CHECKED ORDER BY CHECKED'

       EXECUTE (@SQL)

	   

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 41893462
>>order by checked is fine. But how to group the invoicenumber together
 InvNumber                           Checked
 Number1                  
 Number1
 Number2
 Number2                                
 Number3                                  1
<<

This is not grouped by, if it were there would only be distinct values for InvNumber. Since sql server treats nulls as the lowest values, why not just order by InvNumber, Checked as  Vitor Montalvão suggested? One problem you may run into is Number10 would sort before Number9. That could be dealt with by replacing Number and casting the remaining string to integer.
0
 
LVL 23

Expert Comment

by:Pawan Kumar
ID: 41893881
Hi Rias,

I think you need to provide complete or more information of what you wanted to accomplish here.

Thank you!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41893915
You are trying to build a report. You want a "section heading" that lists the Invoice, and under that you want a series of details

InvNumber                           Checked <<== this is a section heading

Number1                  
Number1
Number2
Number2                                
Number3                                  1

Note also that in the second column you have the word "checked" but also a number (1). SQL requires that columns are the same data type so mixing words and numbers in the same column makes it more difficult.

In short: SQL is NOT a "report builder", instead SQL provides data to products that prepare reports.

How are you using the information? Are you preparing a html page? or, are you using PHP or C# or something similar?
Maybe you want to use something like Crystal Reports or Jasersoft reports?
0
 

Author Closing Comment

by:RIAS
ID: 41894470
Vitor,
Thanks for your solution.
Pawan,
Thanks for the efforts.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now