SQL Statement

[bungpeng]

Using SQL statement, is there anyway I can select all records with field "X" is the biggest values from a table?

 

For example, if there is a field call "Salary" in my "Employee" table, is it possible I can select all the records with the highest salary?

 

Or any references for me regarding the Advance SQL?

 

TQ

[Robby]

The following will get the highest salary ...

SELECT MAX(Salary) AS highest FROM Employee

What do you mean "All the records with the highest salary"?

[bungpeng]

Your SQL will only return the highest Salary value.

 

But what I mean is return those records (employee) who get the highest salary. Salary amount is not important in this case, the important is who get the highest salary.

 

TQ for your replied.

[Robby]

then you need to add a Where clause...

 

Select Salary, EmpName FROM Employee Where Salary > 50000

 

If that does not work then please explain in greater detail.

[bungpeng]

The problem is I don't know what is the highest salary (50000 is your case). Anyway, I was find a solution:

 

SELECT * FROM Employee WHERE (Salary = (SELECT MAX(Salary) FROM Employee))

 

But another problem is, my application is database independent. If I use sub-query as above example, does other database support sub-query? like MySQL, DBase, Oracle...?

[wyrd]

I think sub queries are standard. I don't see why they wouldn't be.

 

A solution I thought of would be using TOP 1 WITH TIES in combination with ORDER BY DESC;

 

SELECT TOP 1 WITH TIES Salary FROM Employee ORDER BY Salary DESC

 

But since you're trying to do this database independant, this would probably be a bad idea. I don't think all DBMS support TOP and/or WITH TIES.

[bungpeng]

"SELECT TOP 1 WITH TIES Salary FROM Employee ORDER BY Salary DESC" return the same result?

 

what is "WITH TIES" mean? I never heard about it... :)

 

Is it better compare with sub-query?

[wyrd]

WITH TIES means that it will keep listing items so long as they have the same values. So if you did something like;

 

SELECT TOP 2 WITH TIES id FROM table ORDER BY id

 

you could get..

 

1

2

2

 

Since there are ties for the last position, it will list all of the rows that are tied for that spot.

 

So..

 

SELECT TOP 1 WITH TIES Salary FROM Employee ORDER BY Salary DESC

 

would give you something like..

 

154000

154000

154000

 

etc.. for as many TIES as there are for the last spot (since you're only doing TOP 1, the last spot is the same as the first). With ORDER BY Salary DESC it will give you the highest salaries.

 

Is this more efficient then your sub-query? It all comes down to with what MAX() does behind the scenes. According to the BOL (assuming I'm reading this right) MAX() will traverse all of the rows regardless if the column has an index or not;

 

"Aggregate functions (such as SUM, AVG, COUNT, COUNT(*), MAX, and MIN) generate summary values in query result sets. An aggregate function (with the exception of COUNT(*)) processes all the selected values in a single column to produce a single result value."

 

If this is indeed true, then my query would be many times faster if your Salary column has an index on it. If you plan on having millions of rows, MAX() may become a bottleneck that you might have to fix.

 

Take all this with a grain of salt. The only real way to find out which query is more efficient is to put it through the query analyzer (assuming you have access to one, which I don't) or to simply fill a table with a million records and start doing some benchmark tests.

[bungpeng]

TQ, I got it now

 

"WITH TIES" only support by SQL server?

[wyrd]

Probably. Only way to find out is to look at the SQL-92 ANSI/ISO Standards.