Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Group by and order by clause

Posted on 2016-11-18
28
Medium Priority
?
63 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30

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

 

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 30

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 30

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 52

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 30

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 52

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
 

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 52

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 52

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 52

Accepted Solution

by:
Vitor Montalvão earned 2000 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 30

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 30

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 49

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

715 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