• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Group by and order by clause

hello,

how to group by col1 and order by col2 in query

Select * from table groupby col1 and order by col2

Cheers
0
RIAS
Asked:
RIAS
  • 15
  • 6
  • 5
  • +2
1 Solution
 
RIASAuthor Commented:
Example:

table1

Col1                           Col2

Number1                  
Number2
Number1
Number2               1
Number3               1


Expected output


Col1                           Col2

Number1                  
Number1
Number2
Number2               1
Number3               1

Cheers
0
 
RIASAuthor Commented:
My col2 is integer. .Arrange it that null remains at top
0
 
Pawan KumarDatabase ExpertCommented:
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
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.

 
RIASAuthor Commented:
Pawan
,
Syntax error near group by
0
 
RIASAuthor Commented:
Can you please  give an example of working query
0
 
Pawan KumarDatabase ExpertCommented:
SELECT col1
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
0
 
RIASAuthor Commented:
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
 
RIASAuthor Commented:
Pawan,
Can you help in my query
0
 
Pawan KumarDatabase ExpertCommented:
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
 
RIASAuthor Commented:
Pawan,
Where is groupby?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
RIAS, I can't see any aggregate function nor the GROUP BY statement in your code.
0
 
RIASAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
SELECT col1, MAX(Col2) col2
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
Vitor,
Gave an error as it says need an aggregate function.
Can you advise
0
 
RIASAuthor Commented:
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
 
RIASAuthor Commented:
Checked is the int column and invoice is for group by
0
 
RIASAuthor Commented:
Sorry invoice number
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
groupby was a guess.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
RIASAuthor Commented:
Sorry :<

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


InvNumber                           Checked

Number1                  
Number1
Number2
Number2                                
Number3                                  1
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
awking00Commented:
>>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
 
Pawan KumarDatabase ExpertCommented:
Hi Rias,

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

Thank you!
0
 
PortletPaulCommented:
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
 
RIASAuthor Commented:
Vitor,
Thanks for your solution.
Pawan,
Thanks for the efforts.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 15
  • 6
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now