Solved

# Group by and order by clause

Posted on 2016-11-18
36 Views
hello,

how to group by col1 and order by col2 in query

Select * from table groupby col1 and order by col2

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

Author Comment

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

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

LVL 18

Expert Comment

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

ID: 41893039
Pawan
,
Syntax error near group by
0

Author Comment

ID: 41893040
Can you please  give an example of working query
0

LVL 18

Expert Comment

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

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)
``````
0

Author Comment

ID: 41893050
Pawan,
Can you help in my query
0

LVL 18

Expert Comment

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)
``````
0

Author Comment

ID: 41893064
Pawan,
Where is groupby?
0

LVL 45

Expert Comment

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

Author Comment

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

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

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

ID: 41893082
Vitor,
Gave an error as it says need an aggregate function.
0

Author Comment

ID: 41893085
my query
``````   DECLARE @SQL NVARCHAR(MAX)
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)) +  '%'''
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)
``````
0

Author Comment

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

Author Comment

ID: 41893088
Sorry invoice number
0

LVL 45

Expert Comment

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

ID: 41893100
groupby was a guess.
0

LVL 45

Expert Comment

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'
``````
0

Author Comment

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

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'
``````
0

LVL 18

Expert Comment

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)) +  '%'''
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)

``````
0

LVL 31

Expert Comment

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

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

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

ID: 41894470
Vitor,
Pawan,
Thanks for the efforts.
0

## Featured Post

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.