Home » SQL & PL/SQL » SQL & PL/SQL » about queries (oracle 9.0.1.8)
| about queries [message #574996] |
Thu, 17 January 2013 10:47  |
 |
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 #575009 is a reply to message #575008] |
Thu, 17 January 2013 14:37  |
 |
Michel Cadot
Messages: 54204
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:00From your previous topic:
Michel Cadot wrote on Sun, 04 November 2012 10:26Welcome 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 |
 |
Littlefoot
Messages: 16993
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 |
|
|
|
Goto Forum:
Current Time: Thu May 23 05:20:49 CDT 2013
Total time taken to generate the page: 0.14153 seconds
|
] 
Members
Search
Help
Register
Login
Home
