Selecting the biggest ZIP code from a column

Question

I want to get the biggest ZIP code in DB. Normally I do this

SELECT * 
FROM (
   Select * From tbuser ORDER BY zip DESC
) 
WHERE rownum = 1

with this code I can get the biggest zip code value without a duplicate row (since zip code is not a primary key).

But the main company at Japan said that I cant use it since when the connection is slow or the DB have very large data, you cant get the right row of it. It will be a great help for me if someone can helps.

Answer 1

I want to get the biggest ZIP code in DB.

If you really only want the zip code, try that:

SELECT MAX(zip) FROM TBUSER;

This will use the index on the zip column (if it exists).

That being said, Oracle is usually smart enough to properly optimize sub-query selection using ROWNUM. Maybe your main company is more concerned about the possible "full table" ̀ORDER BY` in the subquery ? OTH, if the issue is really with "slow network", maybe worth taking some time with your DBA to look on the wire using a network analyzer or some other tool if your approach really leads to "excessive bandwidth consumption". I sincerely doubt about that...

---

If you want to retrieve the whole row having the maximum zip code here is a slight variation on an other answer (in my opinion, this is one of the rare case for using a NATURAL JOIN):

select * from t
natural join (select max(zip) zip from t);

Of course, in case of duplicates, this will return multiple rows. You will have to combine that with one of the several options posted in the various other answers to return only 1 row.

As an extra solution, and since you are not allowed to use ROWNUM (and assuming row_number is arbitrary forbidden too), you can achieve the desired result using something as contrived as:

select * from t
where rowid = (
  select min(t.rowid) rid from t
  natural join (select max(zip) zip from t)
);

See http://sqlfiddle.com/#!4/3bd63/5

But honestly, there isn't any serious reason to hope that such query will perform better than the simple ... ORDER BY something DESC) WHERE rownum <= 1 query.

Answer 2

As you are looking for a way to get the desired record without rownum now, ...

... here is how to do it from Oracle 12c onward:

select * 
from tbuser
order by zip desc fetch first 1 row only;

... and here is how to do it before Oracle 12c:

select * 
from (select tbuser.*, row_number() over(order by zip desc) as rn from tbuser)
where rn = 1;

EDIT: As Sylvain Leroux pointed out, it is more work for the dbms to sort all records rather than just find the maximum. Here is a max query without rownum:

select * 
from tbuser where rowid =
(select max(rowid) keep (dense_rank last order by zip) from tbuser);

But as Sylvain Leroux also mentioned, it makes also a difference whether there is an index on the column. Some tests I did show that with an index on the column, the analytic functions are slower than the traditional functions. Your original query would just get into the index, go to the highest value, pick the record and then stop. You won't get this any faster. My last mentioned query being quite fast on a none-indexed column is slower than yours on an indexed column.

Answer 3

Your query gets exactly one random row of all records having the max zip code. So it cannot be the problem that you retrieve a record with another zip code or more than one record or zero records (as long as there is at least one record in the table).

Maybe Japan simply expects one of the other rows with that zip code? Then you may just have to add another order criteria to get that particular desired row.

Another thought: As they are talking about slow connection speed, it may also be that they enter a new max zip code on one session, query with another and get the old max zip, because the insert statement of the other session hasn't gone through yet. But well, that's just the way this works of course.

BTW: A strange thing to select a maximum zip code. I guess that's just an example to illustrate the problem?

Answer 4

I was wondering that nobody posted this answer yet. I think that is the way, you should do something like that.

SELECT * 
FROM (
   Select a.*, max(zip) over () max_zip
   From tbuser a
) 
WHERE zip=max_zip
and rownum = 1

Answer 5

OK - try something like this:

SELECT *
  FROM TBUSER
  WHERE ZIP = (SELECT MAX(ZIP) FROM TBUSER);

Fetch a single row from a cursor based on the above statement, then close the cursor. If you're using PL/SQL you could do it like this:

FOR aRow IN (SELECT *
               FROM TBUSER
               WHERE ZIP = (SELECT MAX(ZIP) FROM TBUSER))
LOOP
   -- Do something with aRow

   -- then force an exit from the loop

   EXIT;
END LOOP;

Share and enjoy.

Answer 6

IF you are getting multiple records using MAX function (which is not possible, but in your case you are getting, I don't know how until you post screenshot) then You can use DISTINCT in your sql query to get single record

SELECT DISTINCT MAX(zipcode) FROM TableUSER

SQL FIDDLE

Answer 7

Your requirements seem arbitrary, but this should give you the result you've requested.

SELECT * 
FROM (SELECT * FROM tbuser 
      WHERE zip = (SELECT MAX(zip) FROM tbuser)) 
WHERE rownum = 1

Answer 8

This sounds to me like bad advice (masquerading as a rule) from a newbie data base administrator who doesn't understand what he's looking at. That insight isn't going to help you, though. Rarely does a conversation starting with "you're an obstructionist incompetent" achieve anything.

So, here's the thing. First of all, you need to make sure there's an index on your zip column. It doesn't have to be a primary key.

Second, you can try explaining that Oracle's table servers do, in fact, optimize the ... ORDER BY something DESC) WHERE rownum <= 1 style of query. Their servers do a good job of that. Your use case is very common.

But if that doesn't work on your DBA, try saying "I heard you" and do this.

SELECT * FROM (
    SELECT a.* 
      FROM ( SELECT MAX(zip) zip FROM zip ) b
      JOIN ZIP a ON (a.zip = b.zip)
 ) WHERE rownum <= 1

This will get one row with the highest numbered zip value without the ORDER BY that your DBA mistakenly believes is messing up his server's RAM pool. And, it's reasonably efficient. As long as zip has an index.