Home » SQL & PL/SQL » SQL & PL/SQL » about queries (oracle 9.0.1.8)
about queries [message #574996] Thu, 17 January 2013 10:47 Go to next message
engsng
Messages: 5
Registered: November 2012
Location: Istanbul
Junior Member
Hi Guys, I am quite confused about oracle sql for my query. Imagine a table below and I want to write a statement that shows time,name,ip,received and sent (SELECT *). (time, city, ip are primary key lets say).
However there is the condition that I want:
It should be grouped for each CITY and I want to get max(RECEIVED) by descending order for each days. How can I do that? I got an error when I
select * 
from table 
group by city
having max(received)

Is it possible to get output without giving condition to other elements (IP, SENT... etc)
TIME-	        NAME-   IP-     RECEIVED-  SENT-
10.01.2013,	LON,	10	,2342	  ,326
10.01.2013,	PAR,	10	,563463	  ,3
10.01.2013,	LON,	30	,23412	  ,574
11.01.2013,	NY,	20	,36545	  ,246357
11.01.2013,	NY,	40	,23423	  ,5
11.01.2013,	TOK,	40	,45475	  ,3453
11.01.2013,	TOK,	30	,23423	  ,574574
12.01.2013,	LON,	10	,574	  ,2342
12.01.2013,	BAR,	20	,235	  ,7457
12.01.2013,	PAR,	20	,1	  ,23
13.01.2013,	NY,	30	,4574	  ,7356
13.01.2013,	IST,	30	,453	  ,347
13.01.2013,	IST,	10	,457467	  ,2624
13.01.2013,	LON,	40	,3534	  ,73
13.01.2013,	BAR,	10	,342	  ,5

Regards


[EDITED by LF: applied [code] tags]

[Updated on: Thu, 17 January 2013 14:28] by Moderator

Report message to a moderator

Re: about queries [message #574997 is a reply to message #574996] Thu, 17 January 2013 10:51 Go to previous messageGo to next message
BlackSwan
Messages: 23061
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


Why do you continue to ignore Posting Guidelines?
Should we just ignore your questions, too?

[Updated on: Thu, 17 January 2013 10:53]

Report message to a moderator

Re: about queries [message #574999 is a reply to message #574996] Thu, 17 January 2013 11:31 Go to previous messageGo to next message
Michel Cadot
Messages: 59816
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Are you sure you are in Oracle version 9.0.1.8?

Regards
Michel
Re: about queries [message #575008 is a reply to message #574996] Thu, 17 January 2013 14:17 Go to previous messageGo to next message
engsng
Messages: 5
Registered: November 2012
Location: Istanbul
Junior Member
Actually I did not expect such a reaction from senior members of this forum. I was in a hurry in my job and could not write so detailed.

The version is not Im not so sure 9 or 10.
Re: about queries [message #575009 is a reply to message #575008] Thu, 17 January 2013 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 59816
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you think the fact you are in hurry does matter for us who help for free on our rest time?

Note that this is NOT the first time we asked you so as we did it in EACH AND EVERY topic your created:

Michel Cadot wrote on Fri, 30 November 2012 15:00
From your previous topic:

Michel Cadot wrote on Sun, 04 November 2012 10:26
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...



You are free to refuse to follow the rules we chose, we are free to not help you.

Regards
Michel

[Updated on: Thu, 17 January 2013 14:39]

Report message to a moderator

Re: about queries [message #575010 is a reply to message #575008] Thu, 17 January 2013 14:38 Go to previous message
Littlefoot
Messages: 19817
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The fact is: if you post a question asked in a bad manner, you'll (probably) get answers which are the same quality as the question - bad.

Data you posted is - what? Input or output? If it is input and you want someone to work with your data, you should provide test case (CREATE TABLE and INSERT INTO statements). Otherwise, we should type these data manually (someone might do that, though, but most people do not want to spend their time for that).

Query you posted is invalid (that's why you got the error). You should get familiar with language you write code in; guessing syntax isn't the best way to do the job, I suppose.

The best of all - you don't even know database version ... huh? That information might be important because certain versions offer features which do the job easily, but are not available in some other versions.

It's been 4 hours now since you posted the question. If you asked it properly, you might have had the answer already. So, it doesn't really matter what you expected from the rest of the community because it is YOU who needs help, not someone of us.

P.S. This is how I understood Quote:
It should be grouped for each CITY and I want to get max(RECEIVED) by descending order for each days

My output is grouped by JOB and HIREDATE is sorted in descending order (so, obviously, MAX(HIREDATE) per JOB is on a top).

SQL> break on job
SQL> select job, ename, sal, hiredate
  2  from emp
  3  order by job, hiredate desc;

JOB       ENAME             SAL HIREDATE
--------- ---------- ---------- ----------
ANALYST   SCOTT            3800 09.12.1982
          FORD             3800 03.12.1981
CLERK     ADAMS            1900 12.01.1983
          MILLER           2100 23.12.1982
          JAMES            1750 03.12.1981
          SMITH            1600 17.12.1980
MANAGER   CLARK            3250 09.06.1981
          BLAKE            3650 01.05.1981
          JONES            3775 02.04.1981
PRESIDENT KING             5800 17.11.1981
SALESMAN  MARTIN           2050 28.09.1981
          TURNER           2300 08.09.1981
          WARD             2050 22.02.1981
          ALLEN            2400 20.02.1981

14 rows selected.

SQL>

[Updated on: Thu, 17 January 2013 14:50]

Report message to a moderator

Previous Topic: Difference between Object types and collection types
Next Topic: Compilation Issue
Goto Forum:
  


Current Time: Fri Nov 28 03:42:31 CST 2014

Total time taken to generate the page: 0.19819 seconds