Solved

Group by and order by clause

Posted on 2016-11-18
28
36 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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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 45

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
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.

 

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 45

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 45

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 45

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 18

Expert Comment

by:Pawan Kumar Khowal
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 31

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 18

Expert Comment

by:Pawan Kumar Khowal
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

12 Experts available now in Live!

Get 1:1 Help Now