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
how to group by col1 and order by col2 in query
Select * from table groupby col1 and order by col2
Cheers
ASKER
My col2 is integer. .Arrange it that null remains at top
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
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
ASKER
Pawan
,
Syntax error near group by
,
Syntax error near group by
ASKER
Can you please give an example of working query
SELECT col1
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
ASKER
Nope,
JHere is the query
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)ASKER
Pawan,
Can you help in my query
Can you help in my query
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)ASKER
Pawan,
Where is groupby?
Where is groupby?
RIAS, I can't see any aggregate function nor the GROUP BY statement in your code.
ASKER
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
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
SELECT col1, MAX(Col2) col2
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
FROM table
GROUP BY col1
ORDER BY CASE WHEN col2 IS NULL or '' THEN 0 ELSE col2 END
If you don't have any aggregate function then the follow should work.
Column "col2" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Select co1, col2
from table
group by col1, col2
order by col2Column "col2" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
ASKER
Vitor,
Gave an error as it says need an aggregate function.
Can you advise
Gave an error as it says need an aggregate function.
Can you advise
ASKER
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)ASKER
Checked is the int column and invoice is for group by
ASKER
Sorry invoice number
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?
What for you need the GROUP BY clause? Leaving only the ORDER BY clause won't give what you need?
ASKER
groupby was a guess.
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'ASKER
Sorry :<
order by checked is fine. But how to group the invoicenumber together
InvNumber Checked
Number1
Number1
Number2
Number2
Number3 1
order by checked is fine. But how to group the invoicenumber together
InvNumber Checked
Number1
Number1
Number2
Number2
Number3 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
>>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.
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.
Hi Rias,
I think you need to provide complete or more information of what you wanted to accomplish here.
Thank you!
I think you need to provide complete or more information of what you wanted to accomplish here.
Thank you!
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?
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?
ASKER
Vitor,
Thanks for your solution.
Pawan,
Thanks for the efforts.
Thanks for your solution.
Pawan,
Thanks for the efforts.
ASKER
table1
Col1 Col2
Number1
Number2
Number1
Number2 1
Number3 1
Expected output
Col1 Col2
Number1
Number1
Number2
Number2 1
Number3 1
Cheers