I have a table with the following fields:
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
TicketNo varchar2 TName varchar2 DateIssue date/time
I want to retrieve the results in the following form:
DateIssue TName TicketNo ----------- ----- -------- 01-oct-2006 ABC 123-7733 DEF 545-5445 GHI 254-4545 02-oct-2006 JKL 454-7897 MNO 444-7878
TName and TicketNo must be grouped by DateIssue. Since the "Group By" clause uses aggregate functions only, the following type of query will not run:
Select DateIssue,TName,TicketNo from Table1 group by DateIssue order by DateIssue
How to display the above given result?
There are two issues. First, GROUP BY does not necessarily require the use of aggregate functions, although most GROUP BY queries do use them. However, the most important thing about GROUP BY is that it collapses multiple rows into one. This isn't what you want here. You want all your detail rows, but just "grouped" or "arranged" by DateIssue. All you need here is ORDER BY.
select DateIssue , TName , TicketNo from Table1 order by DateIssue
The second issue is what I call "prettification" of the results. This word comes from the verb "prettify," which means "to make superficially pretty."
The simple ORDER BY query will give the following results:
DateIssue TName TicketNo ----------- ----- -------- 01-oct-2006 ABC 123-7733 01-oct-2006 DEF 545-5445 01-oct-2006 GHI 254-4545 02-oct-2006 JKL 454-7897 02-oct-2006 MNO 444-7878
To "suppress" or "hide" the repetitions of the DateIssue value, process the query's result set in your application. This is not something to be attempted with SQL.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.